CODEWITHSUNDEEP
sqlsql-serverperformancedatatablenull
Gill Bates
Gill Bates

Reputation: 51

Do columns with the value NULL impact the performance of Microsoft SQL Server?

I have a datatable with over 200 columns, however, in over half of those columns, the majority of my datarows has the value 'NULL'.

Do those NULL-values decrease the performance of my SQL Server or are fields with NULL-values irrelevant for all actions on the datatable?

Upvotes: 5

Views: 2237

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Performance of the table is basically a function of I/O. The way that SQL Server lays out rows on data pages means that NULL values might or might not take up space -- depending on the underlying data type. SQL Server data pages contain a list of nullability bits for each column (even NOT NULL columns) to keep the NULL information.

Variable length strings simply use the NULL bits, so they occupy no additional space in each row. Other data types do occupy space, even for NULL values (this includes fixed-length strings, I believe).

What impact does this have on performance? If you have 200 NULL integer fields, that is 800 bytes on the data page. That limits the number of records stored on a given page to no more than 10 records. So, if you want to read 100 records, the query has to read (at least) 10 data pages. If the table did not have these columns, then it might be able to read only one data page.

Whether or not this is important for a given query or set of queries depends on the queries. But yes, columns that are NULL could have an impact on performance, particularly on the I/O side of queries.

Upvotes: 3

Tab Alleman
Tab Alleman

Reputation: 31785

Aside from taking up space, and what little impact that may have on performance, they don't have any other effect.

Upvotes: 1

Related Questions