SPARSE Columns :

Introduced in MSSQL 2008, Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve non null values.

The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the values require no storage.

Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.


Limitations :
1. Geography, geometry, image, ntext, text, timestamp and user-defined data types cannot be specified as SPARSE.
2. Sparse column cannot have a default value.
3. Computed column can contain a sparse column, but computed column cannot be marked as SPARSE.
4. Sparse column cannot be part of a clustered index or a unique primary key index. However, both persisted and non persisted computed columns that are defined on sparse columns can be part of a clustered key.

Example :
--  Using create table statement

CREATE TABLE user_sparse 
  ( 
     userid   INT sparse, 
     username VARCHAR(10) sparse, 
     gender   CHAR(1) 
  ) 

-- Using alter table statement

ALTER TABLE user_sparse 
ALTER COLUMN gender  CHAR(1) sparse

-- Check the sparse column status in sys.columns table

SELECT is_sparse, 
       * 
FROM   sys.columns 
WHERE  object_id = Object_id('user_sparse')