SPARSE Column in sql server

What is a SPARSE Column?

Sparse columns provide a highly efficient way of managing empty space in a database by enabling NULL data to consume no physical space. For example, sparse columns enable object models that typically contain numerous null values to be stored in a SQL Server 2008 database without experiencing large space costs.

What is a Column Set?

Tables that contain sparse columns can define a column set to return the data in all of the sparse columns in the table. Similarly to a computed column, it is not physically stored in the table; however, unlike computed columns, the data in it is directly editable. The column set returns an untyped XML representation of the data. Column sets are useful when you have a large number of sparse columns in a table and working with them is awkward.

How to use SQL Server Sparse Columns ?

We can define sparse columns by using either the CREATE TABLE or ALTER TABLE statement, using the SPARSE keyword for the columns that you require to be sparse columns. 

Notes :

Sparse columns require more storage space for non-null values than a standard column.

Data types like geography, geometry, image, ntext, text, and timestamp cannot be used for sparse columns. 

Example: