Sparse Columns in SQL Server

Question

Hi friends I want to know what are sparse columns in SQL Server ? Thank you.

Satyapriya Nayak · Answer

Hi Vipendra 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 nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with column sets and filtered indexes: Column sets INSERT UPDATE and DELETE statements can reference the sparse columns by name. However you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set. For more information about column sets see Use Column Sets . Filtered indexes Because sparse columns have many null-valued rows they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index. For more information see Create Filtered Indexes . Sparse columns and filtered indexes enable applications such as Windows SharePoint Services to efficiently store and access a large number of user-defined properties by using SQL Server 2012. Please refer the below links http://msdn.microsoft.com/en-us/library/cc280604.aspx http://blog.sqlauthority.com/2008/07/10/sql-server-2008-introduction-to-sparse-columns/ http://blog.sqlauthority.com/2008/07/14/sql-server-2008-introduction-to-sparse-columns-part-2/ Thanks