Space used by NULL value in different data type columns

  • Hello friends,

    Recently I red some article about making a column as SPARSE, and the restrictions related to it. Now I would like to know something related to the storage of NULL values.

    After reading the article I understood that if the column is specified as SPARSE it wont take any space for NULL values.

    My doubt is if the column is non-sparse,will there be any change in space used if the value is NULL?

    For example C1 is the column of type INT in a table T_TEST ,

    CREATE TABLE T_TEST

    (C1 INT)

    INSERT INTO T_TEST

    VALUES(10),(20),(NULL)

    SELECT * FROM T_TEST

    As you can see I have inserted values 10,20 and NULL to the column C1 of type INT, is there an difference in space used for these three rows? or each row will use 4 bytes each? I'm really confused about this

    Like that can I know how it will be for varchar,char,nvarchar.

    One more things is is there any way to find the space used by the rows of a table..? my intention is to check the difference in space usage before and after making the column as SPARSE.

    It would be great help if some one can help me in this

    Thanks in advance.

    Thanks & Regards,
    MC

  • each row will use 4 bytes. If it is a nullable column it will have a bit associated with it to indicate that it is null or non-null.

    There is a way to determine the exact size (number of bytes) of each row. It depends on the data types, null-ability and (in the case of varbinary/char) the content of the column (use datalength()) All of this can be computed fairly accurately.

    When you are talking about sparse columns and so forth all that changes.

    The probability of survival is inversely proportional to the angle of arrival.

  • Thanks mate..

    As you mentioned that there is a way to find the exact space used, can I know how to find it..? is there any script for that..?

    And one more confirmation:

    1) If the column is char(10), the max length of the string we can store is 10 bytes and each value (whether its length is < 10 or = 10 ) it will take 10 bytes.

    2)If the column is varchar(10) again max length of the string we can store is 10 bytes but the space will be length(string) bytes

    3)If the column is nvarchar(10), each character will take 2 bytes , so the max length of string we can store is 5 , and the space used will be length(string) * 2 bytes

    I'm I correct about the above three points?

    Thanks & Regards,
    MC

  • only4mithunc (11/17/2010)


    And one more confirmation:

    1) If the column is char(10), the max length of the string we can store is 10 bytes and each value (whether its length is < 10 or = 10 ) it will take 10 bytes.

    2)If the column is varchar(10) again max length of the string we can store is 10 bytes but the space will be length(string) bytes

    3)If the column is nvarchar(10), each character will take 2 bytes , so the max length of string we can store is 5 , and the space used will be length(string) * 2 bytes

    I'm I correct about the above three points?

    Correct. In the case of [n]varchar/varbinary there are two additional bytes that store the length. In the case of [n]varchar(MAX) there are a few additional bytes needed to support these (I am not certain of the exact number)

    The probability of survival is inversely proportional to the angle of arrival.

  • On point #3. If NVARCAHR(10) you can still store 10 characters (not just 5). But, yes they each take up twice the space of a non-unicode string.

  • Thanks for the clarification.

    And as I asked above, can I know how can we find the exact space used...? Is there any script for that..?

    My aim is if there any script for that I can check the space used , before and after making the column as SPARSE...

    Thanks & Regards,
    MC

  • My understanding is that you want to find out if there's a difference if between a normal column and after you make it a sparse column. The easiest way would be to us sp_spaceused on the table before and after the change and simply calculate the difference between the two results.

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

  • Thanks Jeff,

    I have tried the same by using the below example. I have created a table with two columns then inserted 1000 records with NULL for one column, then checked using sp_spaceused, after that I have altered column( to which I have inserted NULL ) to SPARSE, but then I checked sp_spaceused it didn't show any difference.

    CREATE TABLE TEST_SPARSE

    (

    C1 INT,

    C2 INT

    )

    GO

    DECLARE @v-2 INT = 0

    WHILE (@V<1000)

    BEGIN

    INSERT INTO TEST_SPARSE

    SELECT 10,NULL

    SET @v-2 = @v-2 +1

    END

    ALTER TABLE TEST_SPARSE

    ALTER COLUMN C2 INT SPARSE

    But when I dropped the table and the recreated with the column as SPARSE , then after inserting the same records , sp_spaceused has showed some difference.

    CREATE TABLE TEST_SPARSE

    (

    C1 INT,

    C2 INT SPARSE

    )

    GO

    DECLARE @v-2 INT = 0

    WHILE (@V<1000)

    BEGIN

    INSERT INTO TEST_SPARSE

    SELECT 10,NULL

    SET @v-2 = @v-2 +1

    END

    Is this correct? then how SPARSE will be useful if we are altering an existing column AND making it SPARSE..?

    Thanks & Regards,
    MC

  • Friends,

    Did you get a chance to look into my previous reply? 🙁

    I have created a table with two columns then inserted 1000 records with NULL for one column, then checked using sp_spaceused, after that I have altered column( to which I have inserted NULL ) to SPARSE, but then I checked sp_spaceused it didn't show any difference.

    Thanks & Regards,
    MC

  • only4mithunc (11/21/2010)


    Friends,

    Did you get a chance to look into my previous reply? 🙁

    Mostly yes. You forgot to do two things in your testing. You really do need a (usually PK) column with a clustered index on it. After you change the non-clustered column to SPARSE, you need to rebuild the already allocated rows in the table by rebuilding the indexes on the table. Then test using sp_space used.

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

  • Thanks a lot Jeff,

    When I tried those two things, I'm able to see the difference... 🙂

    Thanks & Regards,
    MC

  • only4mithunc (11/21/2010)


    Thanks a lot Jeff,

    When I tried those two things, I'm able to see the difference... 🙂

    And thank you for the feedback.

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

Viewing 12 posts - 1 through 11 (of 11 total)

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