SQL Server nullable values storage impact

Question

In SQL server, do null values occupy less space than non-null values, or is null represented as a flag indicating that no value is stored (and thus actually requiring MORE space to store null values).

The comparisons would not be null values vs. not null values stored in the same column, but null values stored in a nullable column and actual values stored in a not-null column.

I understand it may be common knowledge that NULL occupies less space, but with optimization, is there an actual significant impact?

Answer 1

The NULL flag is stored at the beginning of each row, but the structure on disk is a repeating structure of a fixed size.

In other words if you have

Create Table XX
(
   Id Int Identity (1, 1)
   FirstName VarChar (20) NULL,
   LastName Varchar (20) NOT NULL
)

The same amount of space will be occupied by each row regardless of how much data you put into it.

Answer 2

NULLs for variable length columns are stored in the NULL bitmap which is present in every record since SQL Server 2000 at least: no space is used for the column itself. (Edit: 2 bytes for length which will be zero of course)

For fixed length columns, the NULL bitmap means no sentinel value is needed in the space consumed by storage of the fixed length column.

Indexing aside, NULL comparisons can be quicker because of this, and because of how comparisons to NULL are always UNKNOWN (which drops to false)

Edit:

Paul Randall's Inside the Storage Engine: Anatomy of a record which shows on-disk structure + explains the null bitmap optimization + how fixed and variable length columns are stored

Edit 2: reading the question again...

From a storage perspective, the NULL bitmap perhaps won't be an optimization because it adds a byte (or several). However, in practice it avoids a lot of processing to find NULL values.

Answer 3

Null values are stored in flags because of a simple reason:

Suppose that in your table you have null values and they occupy no space, this is very pretty, but once you put some data in that space, you need to move all data behind it to make room for the new data, and the cost of this is greater that the cost of not previusly allocating hard drive space for the nullable field.

Long story short, databases are intended for environments with no problems on hdd, so optimizations this small are not very important, and thus every record has a nullable flag in it, if its true, the record is null, else it has a value.

Hope my explanation helps

Best of Luck!

Answer 4

Commenter pointed to another stack overflow question containing answer.

Also, I verified this with a co-worker at work.

Thank you for all of your answers.