Storage of NULL values in SQL Server

Earlier today a couple of team members asked whether NULL values in a database take up any space. I wasn't sure of the answer so I did a little research which I decided to share here in case anyone is interested.

The quick answer is no, NULL values don't take up any additional space. However, each column does take up a certain amount of space whether or not it contains any data. Fixed datatype columns (int, float, char, etc.) take up a specific amount of space depending on the size of the datatype (int=4 bytes, smallint=2 bytes, float=4 or 8 bytes). Variable length columns (varchar, varbinary) take up a minimum of 2 bytes per column even if empty (to store the actual length pointer).

The NULL status of each column is stored in a special part of the row called the NULL bitmap that contains one bit for each column, in 8-bit chunks. So a table with 1-8 columns has a one byte (8 bit) NULL bitmap but a table with 9-16 columns has a two byte (16 bit) NULL bitmap.

If you’re interested in the detailed version, be sure to check out Inside Microsoft SQL Server 2005: The Storage Engine, which I highly recommend with the rest of the series.