sql server
There has always been an almost religious argument between DBA’s over nullable columns. Should you use them or should you not? NULL means nothing is stored in a column. Not spaces, not zeroes, just nothing. NOT NULL means a value must be stored in the column. Using NULL may save space but causes aggravation for developers as they have to check the column for NULLs before using that column. Now with Sparse columns, Column Sets and Filtered Indexes, you may choose to use nullable columns more often to save storage and improve performance. These features have been enhanced in R2. Sparse columns are intended for columns that have a large number of NULL values in the data. By specifying a column as SPARSE, you are telling SQL Server to optimize storage based on NULL values. Non-Null values will have a slightly higher overhead but based on the many NULL values, overall you will save significant storage. So the question arises, at what point should we consider using sparse columns? Books Online has a good article on just this subject. “Using Sparse Columns” has a chart that tells us the threshold percentage of NULL values in a table that will yield 40% storage space savings. For instance, if over 64% of the values in an integer data type are NULLs then it may be worth your while to make that column sparse. For datetime data types, the threshold is 52%. Another feature that can be used with sparse columns is the Column Set. This is where we have a large number of sparse columns in a table and we want to process the sparse columns together as a set rather than individually. A column set is an untyped XML column that contains the sparse column values in a row in one XML document for easy access. The Column Set takes no storage, as it is derived at run-time, kind of like a calculated column. However, unlike a calculated column, a Column Set is updateable using the values directly in the XML document. Yet another feature that may be useful with sparse columns is the Filtered Index. You can now create an index on a subset of rows in a table, instead of all the rows so can save storage and improve performance. You can now add a WHERE clause to an index definition. Building an index on the rows that have non-null values in a sparse column would maximize the benefits. Filtered Indexes can be created using non-sparse columns too. Maybe this will settle the NULL vs NOT NULL argument once and for all, or at least reduce it to “it depends”. Or maybe it will just fuel the fire based on normalization violations. We’ll see… cheers Brian
