Is NULL value takes storage space?

If you think that a column defined as INT NULL
will not allocate any disk space when there are no actual values
inserted into it,You are wrong!

All fixed-length columns reserve space in the row regardless
of whether or not they allow NULL value.Unless they defined as
SPARSE.

For variable size datatypes the acctual size is 0 bytes in case
there is a NULL value.

For example, if you have a INT column, the record will always
contain 4 bytes to store the value.

As long as at least one column in a table is nullable,
there is Null Bitmap for each row that specifies what column contains
a NULL value in a column.The null bitmap contains bit for each column.
There is a common myth that the null bitmap only contains bits
for nullable columns.It is not true,it contains bits for each column.

In case some column is NULL the bit in the null bit map for this
column will be set to indicate that the column is null, and the
data will not be looked at.

For more information:
http://www.sqlskills.com/BLOGS/PAUL/post/Misconceptions-around-null-bitmap-size.aspx
http://weblogs.sqlteam.com/mladenp/archive/2007/09/06/How_does_SQL_Server_really_store_NULL-s.aspx

Tweet