Sparse Columns

Sparse columns are another addition to the SQL Server 2008 tool belt. They offer a good trade-off for many applications: taking no space if they are empty and more space if they have data. In other words they optimize storage for NULL values.

Sparse columns are just like ordinary columns with a few limitations. They are defined with the SPARSE keyword and there is no difference on how they are used in data manipulation statements.

Here are the details:

Pros:
• Storing NULL in a sparse column takes up no space at all
• Up to 30,000 columns
• To any external application the column will behave the same
• Sparse columns fit well with filtered indexes to index on non-NULL values

Cons:
• If a sparse column has data it takes 4 more bytes than a normal column
• Not all data types can be sparse: TEXT, NTEXT, IMAGE, TIMESTAMP, user-defined data types, GEOMETRY, GEOGRAPHY, and VARBINARY(MAX) with the FILESTREAM attribute
• Computed columns cannot be sparse
• Cannot have default values

Here is example of creating and using sparse columns.

CREATE TABLE Survey (
  survey_nbr INT NOT NULL PRIMARY KEY,
  survey_desc VARCHAR(30),
  survey_info1 VARCHAR(30) SPARSE NULL,
  survey_info2 VARCHAR(30) SPARSE NULL,
  survey_info3 VARCHAR(30) SPARSE NULL,
  survey_info4 VARCHAR(30) SPARSE NULL);
 
INSERT INTO Survey(survey_nbr, survey_desc, survey_info1) 
VALUES(1, 'Survey 1', 'some data'),
      (2, 'Survey 2', 'good');


Tables with sparse columns can have defined column set to manipulate all sparse columns as a structure. The column set is untyped XML column and is treated as any other XML column.

Here are the characteristics of column sets:

• Defined as XML data type column
• Not stored, like computed column, but updatable
• Only one column set per table
• Cannot be changed
• Cannot be added if the table already has sparse columns
• Limited by XML data size limit of 2 GB (all sparse columns in a row cannot exceed 2 GB)
• Cannot define constraints or default values
• Allows to update or insert sparse column values
• On update all sparse columns are updated, when no value provided sets the column to NULL

Follows example of creating a column set and inserting data into sparse columns using the column set.

CREATE TABLE Survey (
  survey_nbr INT NOT NULL PRIMARY KEY,
  survey_desc VARCHAR(30),
  survey_info1 VARCHAR(30) SPARSE NULL,
  survey_info2 VARCHAR(30) SPARSE NULL,
  survey_info3 VARCHAR(30) SPARSE NULL,
  survey_info4 VARCHAR(30) SPARSE NULL,
  survey_set XML column_set FOR ALL_SPARSE_COLUMNS);
 
-- Insert using the regular sparse columns
INSERT INTO Survey(survey_nbr, survey_desc, survey_info1) 
VALUES(1, 'Survey 1', 'some data'),
      (2, 'Survey 2', 'good');
 
-- Insert using the column set
INSERT INTO Survey(survey_nbr, survey_desc, survey_set) 
VALUES(3, 'Survey 3', '<survey_info3>data 3</survey_info3><survey_info4>answer 4</survey_info4>');


Any mistyping of column name in the XML column set or providing invalid data type value will result in error as when manipulating directly the columns.

Resources:

Using Sparse Columns
http://technet.microsoft.com/en-us/library/cc280604(SQL.100).aspx

Using Column Sets
http://technet.microsoft.com/en-us/library/cc280521(SQL.100).aspx

Labels: ,