August 2, 2021 at 7:03 am
Does XML datatype with NULL Value in SQL Server 2008 consume any storage space?
As mentioned in https://www.sqlshack.com/optimize-null-values-storage-consumption-using-sql-server-sparse-columns/
If we store a NULL value in a fixed-length column such as a column with INT data type, the NULL value will consume the whole column length.
If we store a NULL value in a variable-length column such as a column with VARCHAR data type, it will consume only two bytes from the column’s length.
As mentioned in https://www.sqlshack.com/an-overview-of-sql-server-data-types/
XML datatype is neither Fixed Length nor Variable Length. It comes under other datatype category.
What is the storage space consumed by the XML datatype with NULL Value?
August 2, 2021 at 12:04 pm
Actually, that article is a little off in what it's describing. It's not about fixed length versus not fixed length columns. It's about character (CHAR) versus variable character (VARCHAR). By using the phrase "fixed length" it makes it sound like integer, as you described, fills in the full value of 8 bytes of storage on every NULL. That's not true. It is true of a CHAR column though. That's what was meant. After all, if you make something VARCHAR(20), then that is a fixed length. It can only store up to 20 characters. However, while being fixed length, it's not filling stuff in.
In your case, XML, basically works like a VARCHAR(MAX) column. Some of the data can be stored on the page with the index or heap. The rest will go to secondary storage, up to 2gb in size. A null doesn't fill in 2gb. It's nothing but a descriptor that the column is null. I haven't tested it, but I'll bet it's the same as a null varchar.
I hope that helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 2, 2021 at 3:17 pm
Are you on Standard or Enterprise Edition?
If on Enterprise Edition, you can use data compression to reduce the size of the data stored. Row compression is very little overhead but can still reduce NULL columns to definitely using no space (in the vast majority of columns, at least).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 2, 2021 at 3:49 pm
Does XML datatype with NULL Value in SQL Server 2008 consume any storage space?
As mentioned in https://www.sqlshack.com/optimize-null-values-storage-consumption-using-sql-server-sparse-columns/ If we store a NULL value in a fixed-length column such as a column with INT data type, the NULL value will consume the whole column length. If we store a NULL value in a variable-length column such as a column with VARCHAR data type, it will consume only two bytes from the column’s length.
As mentioned in https://www.sqlshack.com/an-overview-of-sql-server-data-types/ XML datatype is neither Fixed Length nor Variable Length. It comes under other datatype category.
What is the storage space consumed by the XML datatype with NULL Value?
That's actually not the real problem that you're going to/are having. The real problem is that XML is a LOB datatype that's just about the same as VARCHAR(MAX) (as an example). This issue started way back in 2005 when MS came out with the new LOB datatypes... they defaulted them to "In Row" instead of "Out of Row".
There are three serious problems that causes...
That means that the NULLs you're concerned about are also a part of the problem because they are part of what enables all the problems above to occur.
To summarize the fix for all that, you have to set the table option to have LOBs out of row and you have to default the LOB column to a single space so that the pointer that the 16 byte pointer will be created so that no "ExpAnsive" Updates will occur in the future. It's usually a very worthwhile investment in additional bytes. If the table already has data in it, you'll also have to do an "in-place" update on the column (update the column to itself) in order to get the existing data to move out of row. Any new data will be moved auto-magically.
And, no... there is no database wide setting for the table out of row option. You have to do it for each table when you design the table or when you want to update a table to have the option.
There are also a couple of caveats in answering your question. If the other variable width columns that are physically to the right of a null variable width column are also null, then the column will take no bytes. If, however, any of the columns to the right are not null, then the column in question will contain 2 bytes even if null to indicate that the length of the column is zero. That also doesn't include any bits in nulllable columns fields of the row header. It also isn't documented anywhere that that's what happens. I've only recently discovered it and tested it (like 3 weeks ago) as a proof.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2021 at 5:28 am
As we now know that XML NULL value takes no space , We would like know the space taken when values(non NULL values) are stored in a column with XML datatype.
As mention in the Link, https://www.sqlshack.com/an-overview-of-sql-server-data-types/
Integer datatype takes 4 bytes of storage and Small Integer datatype takes 2 bytes of storage. Similarly we would like to know
How much space does XML datatype take?
August 3, 2021 at 6:02 am
Empty string (not NULL, empty string) of XML data type takes 9 bytes.
Single character XML occupies 13 bytes.
Every extra character takes extra 2 bytes.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply