How are NULL values stored

[GysE]

We want to know what the impact of NULL values is on physical space sizes on the disk.
Is there any documentation to prove when you got an INT value and you store a NULL value in there that it will still take up 4 bytes.

 

[NathanGriffiths]

Hi,

As far as I know an INT column will take up 4 bytes whether or not it is NULL or a value. There is a storage engine overhead in allowing NULLs as SQL Server keeps a bitmap for each row indicating which nullable columns actually are NULL. If NULLs are allowed SQL Server must decode this bitmap for every row accessed.

(see "Inside Microsft SQL Server 2000 by Delaney p.235)

Nathan

 

[SQLBill]

Check the BOL, search the index for 'data types-SQL Server, field length'. The default value for int (native format) is 4 characters (and nullable data is the same length as non-nullable data).

-SQLBill