Mohamed Zaatar's Blog

Spare column is a normal column with storage optimized. Simply when the value in the column is NULL , it takes no space. It can be used for columns and filtered indexes (where index will index only the rows with values populated).

There are some SQL Data Types can't be used with Sparse , they are:
- geography
- geometry
- ntext
- text
- image
- timestamp
- varbinary
- user-defined data type

Restrictions about using Sparse Column:
- Sparse Column must be nullabe.
- Sparse Column can't have IDENTITY or ROWGUIDCOL or FILESTREAM or Default value attributes.
- Sparse Column can't be bounded to a rule.
- Sparse Column cant be computed column.
- Sparse Column cant be part of clustered index or PK but can be used in non-clustered index.
- Sparse Column is incompatible with data compression so can't compress a table with sparse column(s).