How much size "Null" value takes in SQL Server

Viewed 77080

I have a large table with say 10 columns. 4 of them remains null most of the times. I have a query that does null value takes any size or no size in bytes. I read few articles some of them are saying :

http://www.sql-server-citation.com/2009/12/common-mistakes-in-sql-server-part-4.html

There is a misconception that if we have the NULL values in a table it doesn't occupy storage space. The fact is, a NULL value occupies space – 2 bytes

SQL: Using NULL values vs. default values

A NULL value in databases is a system value that takes up one byte of storage and indicates that a value is not present as opposed to a space or zero or any other default value.

Can you please guide me regarding the size taken by null value.

5 Answers

Even though this questions is specifically tagged as SQL Server 2005, being that it is now 2021, it should be pointed out that it is a "trick question" for any version of SQL Server after 2005.

This is because if either ROW or PAGE compression are used, or if the column is defined as SPARSE, then it will "no space" in the actual row to store a 'NULL value'. These were added in SQL Server 2008.

The implementation notes for ROW COMPRESSION (which is a prerequisite for PAGE COMPRESSION) states:

NULL and 0 values across all data types are optimized and take no bytes1.

While there is still minimal metadata (4 bits per column + (record overhead / columns)) stored per non-sparse column in each physical record2, it's strictly not the value and is required in all cases3.

SPARSE columns with a NULL value take up no space and no relevant per-row metadata (as the number of SPARSE columns increase), albeit with a trade-off for non-NULL values.

As such, it is hard to "count" space without anlyzing the actual DB usage stats. The average bytes per row will vary based on precise column types, table/index rebuild settings, actual data and duplicity, fill capacity, effective page utilization, fragmentation, LOB usage, etc. and is often a more useful metric.

1 SQLite uses a similar approach to have effectively-free NULL values.

2 A brief of the technical layout used in ROW (and thus PAGE) compression can found in "SQL Server 2012 Internals: Special Storage".

Following the 1 or 2 bytes for the number of columns is the CD array, which uses 4 bits [of metadata] for each column in the table to represent information about the length of the column .. 0 (0×0) indicates that the corresponding column is NULL.

3 Fun fact: with ROW compression, bit column values exist entirely in the corresponding 4-bit metadata.

Related