Sparse Column in SQL Server

What is sparse column?

• It is a method to store the data for a column in most efficient way.
• It is useful for those column which comparatively having more values as “NULL”.
• So in other words, we can say, it is a optimized storage method for NULL values.
• In the case of sparse column, when the column value is NULL for any row in the table, the values require no space.
• A column can be defined as SPARSE, by using the “CREATE TABLE” or “ALTER TABLE” statements, for example :- 

CREATE TABLE tbl ( col VARCHAR(100) SPARSE NULL

                                    )

• SPARSE column can take advantage of filtered Indexes, where data are filled in the row.

What is the need to set a column as sparse column or why can we not set all columns as sparse column as it is the optimized way to store the NULL values?

So here is the answer, Sparse Columns require 4 extra bytes of storage for each non NULL fixed-length data type value in the table and zero bytes to store a NULL value. So it is important to justify the need of making any column as sparse column else we will end up with using of more spaces.

In normal scenario, Consider using sparse columns when the space saved is at least 20 percent to 40 percent. 

Limitation of SPARSE column:-

• SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined data types.
• SPARSE column does not have IDENTITY or ROWGUIDCOL property.
• SPARSE column cannot have default value or rule or computed column.
• Clustered index or a unique primary key index cannot be applied SPARSE column. SPARSE column cannot be part of clustered index key.