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