What uses have you found for sparse columns in your databases?

SPARSE columns is a new feature in SQL Server 2008. This feature enables developers/DBAs or others who work with SQL Server database to store null values very effectively. In ideal scenario, if the column is NULL, than with this feature being added in the database, data will not be stored in the database table containing the sparse column and it is very cost effective solution for saving the space. Null values in the sparse columns take no space at all. It is a good practice to save the space to opt this solution if any table has lot of NULL values. Sparse columns have two important features and they are Column Sets and Filtered Index.

Column Sets: Columns set is another feature introduced in SQL Server 2008 along with Sparse Columns. A column set is actually same as calculated column except that we can update the data of that column. This feature basically gives us the report of all the sparse columns.

Filtered index: A filtered index is a non-clustered index that is defined on a subset of data. Generally, with this feature, Index will be created on the not null data in the sparse columns. Basically we can create a filtered index in the table excluding null values.

SPARSE Columns Limitations: There are limitations of using Sparse Columns though, and these limitations are:

o Microsoft recommends that you use sparse columns only when the space saved is at least 20 to 40%.

o Columns must be nullable and cannot be configured with the ROWGUIDCOL or IDENTITY properties.

o Columns cannot include a Default.

o Sparse Columns should not bound to a rule.

o We cannot define a column as sparse if it is configured with certain data types, such as TEXT, IMAGE, or TIMESTAMP.

o A 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.

Here is the reference link if further detail information is needed on Sparse Columns: http://msdn.microsoft.com/en-us/library/cc280604.aspx

How it works: We can add the key word Sparse in a CREATE TABLE statement as shown below:

CREATE TABLE SparseTestCustomer

(

ID int identity (1,1) primary key,

CustomerFName varchar(30),

CusttomerMName varchar(10),

Address varchar(50) SPARSE NULL,

CustomerPhoneNo varchar(12) NOT NULL

)

When we select this table after inserting the record, it will be like normal columns.