locked
Size of Nulls RRS feed

  • Question

  • Hello Friends

    I was just going through the size occupies by the datatypes of sql server

    but i am wondering how much size a NULL can occupies? can any one suggest how much  NULL s occupies a hard disk.. space?

    and also the method to find out the size occupied by NULLs

    Thanks in advance..

    Paresh.

    Thursday, January 18, 2007 8:23 PM

Answers

  • Hi,

    The NULL value itself does not use any additional storage. When a column is set as NULLABLE, somekind of flag is used to indicate that the field is NULL or not. Therefor, there is no difference whether you use a NULL for a variable length datatype or for a fixed length datatype.

    So it is at the record level that you can see how much size the NULL fields will occupy by using the rule (Null_Bitmap = 2 + ((Num_Cols + 7) / 8)) as mentioned above.

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    Monday, January 22, 2007 7:51 AM

All replies

  • Hi,

    When calculating the size of a record you need to take in account some rules. For null values you need to reserve at least 2 bits.

    http://msdn2.microsoft.com/en-us/library/ms189124.aspx (see point 3)

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    Thursday, January 18, 2007 9:08 PM
  • Hello there..

     

    Thank you for your reply it was helpful..

    but my question is still there.. if I'm taking any non variable datatype as NULL in my database then how much space it will occupy? and also if its variable length datatype then how much space it will occupy?

    Monday, January 22, 2007 7:20 AM
  • Hi,

    The NULL value itself does not use any additional storage. When a column is set as NULLABLE, somekind of flag is used to indicate that the field is NULL or not. Therefor, there is no difference whether you use a NULL for a variable length datatype or for a fixed length datatype.

    So it is at the record level that you can see how much size the NULL fields will occupy by using the rule (Null_Bitmap = 2 + ((Num_Cols + 7) / 8)) as mentioned above.

    Greetz,

    Geert

     

    Geert Verhoeven
    Consultant @ Ausy Belgium

    My Personal Blog

    Monday, January 22, 2007 7:51 AM
  • Hey Geert,

     

    Thank you very much, that was really helpfull.

    Paresh.

    Tuesday, January 23, 2007 12:58 PM