SQL Server Performance Tuning – Using NULL values in Table columns

By Santhosh N

This explains the performance overhead of using NULL values in the columns of the Database table.

Using NULL values has the following drawbacks:

1) Using NULL values in the Database is always going to be overhead in terms of querying the tables’ columns containing the NULL values especially the larger tables. This is because, to query the column which has the NULL values from the table, index won’t be used as we need to query using IS NULL clause. As a result full table scan is performed.

2) When NULL value is used as one of the value for the column, it takes up the whole length of the column size if the column is of fixed length. However, using variable length column does not have this impact but otherwise with fixed length columns, always to store NULL value it occupies the entire column length resulting in growing the Database size which is totally unnecessary.

3) Using NULL values in the Columns of the tables lead to convoluted Transact SQL code resulting in code not running efficiently or error prone.

Hence, considering above, always try to avoid using NULL values and instead one can use some constants to represent NULL values like N/A.

Related FAQs

This describes the usage of DBCC SHOW_STATISTICS command which is used for performance tuning in the Sql Server.
You could check if the index created on the table is being used or not by monitoring that and queried
This explains the difference between the Clustered Index and Non Clustered Index.
SQL Server Performance Tuning – Using NULL values in Table columns  (3637 Views)