Storage Space of a Column having XML datatype with NULL value

  • 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?

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • IT researcher wrote:

    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...

    1. Totally unnecessary extreme bloating of Clustered Indexes (and Heaps)
    2. Massive amounts of page splits in Clustered Indexes and forwarded rows in Heaps due to "ExpAnsive" Updates
    3. Potentially (but frequently) huge amounts of wasted space in Clustered Indexes/Heaps in the form of what I call "Trapped Short Rows" where pages can permanently have ultra low page densities even as low as 1% and less.

    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


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • 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?

  • 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 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply