Saturday, November 27, 2010

Sparse columns do not take any space

Sparse columns are the new feature in SQL SERVER 2008. Sparse columns are better, when dealing with NULL in SQL SERVER. Sparse columns do not take any space. Defining the column as sparse can save a significant amount of disk space but at the cost of more overhead to retrieve non null values. Sparse columns can be defined by using the CREATE TABLE or ALTER TABLE statements.

CREATE TABLE DocumentStore
Title varchar(200) NOT NULL,
ProductionSpecification varchar(20) SPARSE NULL,
ProductionLocation smallint SPARSE NULL,
MarketingSurveyGroup varchar(20) SPARSE NULL 
) ;

Advantages of Sparse Columns
  • Storing a null in a sparse column takes up no space at all.
  • Sparse Columns will behave as the ordinary columns; SPARSE column can work as one XML column as well.
  • Sparse columns work really well with filtered indexes, where data are filled in the row. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index
  • SPARSE column saves database space when there are null values in database.
Disadvantages of Parse Columns
  • SPARSE column does not have IDENTITY or ROWGUIDCOL property.
  • Sparse Column must be null able
  • SPARSE column cannot be applied on text, ntext, image, timestamp, geometry, geography or user defined data types.
  • SPARSE column cannot have default value or rule or computed column.
  • Clustered index or a unique primary key index cannot be applied on SPARSE columns. SPARSE column cannot be part of clustered index key.
  • Table containing SPARSE column can have maximum size of 8018 bytes instead of regular 8060 bytes.
  • A table operation which involves SPARSE column takes performance hit over regular column.
  • Sparse columns are incompatible with data compression (Data compression doesn't work).
  • Merge replication does not support sparse columns

No comments:

Post a Comment

Share This