Sparse Columns In MsSqlServer

1)   What are Sparse Columns and Their Usage

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:

Sparse Columns store data in XML Columns.

Rememering Point about Sparse Columns

1)     It could not Identity.

2)     It Could not  large memory objects,spatial data types

3)     It could not rowguidcol

4)     Rule and default does’nt apply on sparse columns

5) Using sparse columns reduces the maximum size of a row from 8,060 bytes to 8,018 bytes.

  • Properties of Sparse Columns

Sparse columns have the following characteristics:

  • 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.
  • Catalog views for a table that has sparse columns are the same as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.
    • When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.
    • When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.
    • For insert operations, all bits are set to 1.

SQL Server Technologies That Support Sparse Columns

Transactional replication
Transactional replication supports sparse columns, but it does not support column sets, which can be used with sparse columns.

Merge replication
Merge replication does not support sparse columns or column sets.

Change tracking
Change tracking supports sparse columns and column sets.

Change data capture
Change data capture supports sparse columns, but it does not support column sets

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s