I was wondering what exactly you mean by “this allows an optimization when reading columns that are NULL”. Could you elaborate on that?

Firstly, having a null bitmap removes the need for storing special ‘NULL’ values for fixed-length datatypes. Without the null bitmap, how can you tell whether a column is NULL?

That’s easy for a variable-length column – just check the length. If it’s zero, then the column is NULL.

[Edit] Ryan Stonecipher (the dev responsible for DBCC) pointed out that I’d forgotten the case of empty strings – thanks Ryan. In this case, an empty string also has a zero length so for varchar columns you’d need to use the fixed-length solution described below.

It’s not so easy for fixed-length columns, which, as their name suggests, have a fixed-length so that trick doesn’t work. The only solution is to define a special ‘NULL’ value, which limits the effective range of the datatype being stored.

Secondly, it saves CPU cycles. If there was no NULL bitmap, then there are extra instructions executed for fixed- and variable-length columns.