NULL Overhead

Learn about how the use of the NULL condition affects SQL Server performance.

Michelle A. Poolet

April 19, 2006

1 Min Read
ITPro Today logo

Varying amounts of database-engine overhead are associated with NULL, depending on which version of SQL Server you're using. SQL Server 2005, SQL Server 2000, and SQL Server 7.0 handle NULL conditions far more efficiently than SQL Server 6.5 did.

SQL Server 6.5 treated nullable columns as though they were variable-length data. If a row in SQL Server 6.5 contained variable-length data types or nullable columns of another data type, SQL Server added at least nine bytes to the row for administrative overhead.This was one byte more than twice the number of bytes that SQL Server 6.5 added to a row containing only fixed-length data types. This extra five-plus bytes per row could adversely affect I/O operations, minimizing the number of records that could be read into the data cache with each file I/O.

Starting with SQL Server 7.0, NULL handling was significantly streamlined. SQL Server 2005, 2000, and 7.0 treat a fixed-length nullable column as a fixed-length data type, not as a variable-length data type, and store a special NULL bitmap in every record.The bitmap identifies which of the columns are nullable—0 or 1—even for rows in which every column is NOT NULL. Every time the row is accessed, the processor has to decode the bitmap. Much of the overhead involved with NULL processing in SQL Server 2005, 2000, and 7.0 is processor time. You can mitigate this overhead by upgrading the processor on your SQL Server system.

Sign up for the ITPro Today newsletter
Stay on top of the IT universe with commentary, news analysis, how-to's, and tips delivered to your inbox daily.

You May Also Like