Sparse Column – Sql Server 2008 Features

Today, we are discuss ‘Sparse column’ features in sql server 2008.

Sparse column will not use any space for NULL value. Some advantages and disadvantages of sparse column are as follows.

Advantages :

  • Storing Null value takes up no space at all.
  • Sparse columns work really well with filtered indexes as you will only want to create an index to deal with non-null values.
  • You can add 30,000 columns as sparse column in table well regular columns has limit of 1024.

Disadvantages :

  • It will take 4 extra bytes space to store non null values in it.
  • Sparse column can’t be associated with the data types CHAR,NCHAR,IMAGE,TIME STAMP,GEOMETRY,GEOGRAPHY and USER DEFINES DATA TYPE.
  • Data compression doesn’t work.
  • You can’t apply rules.
  • Sparse doesn’t have default values.