SQL Server Sparse Columns

SQL Server Sparse Column

Often stored in the columns of a reporting database are troublesome columns that are populated with limited amounts of data.  One method to optimize these types of columns in your reporting database is to utilize the SPARSE keyword in a CREATE or ALTER table statement.  For instance to mark the Product Location as sparsely populated you would use the following script.

CREATE TABLE DocumentStore
    (DocID int PRIMARY KEY,
     Title varchar(200) NOT NULL,
     ProductionSpecification varchar(20) SPARSE NULL,
     ProductionLocation smallint SPARSE NULL,
     MarketingSurveyGroup varchar(20) SPARSE NULL ) ;
GO

When you enable a column as a SPARSE column you are optimizing the way SQL Server stores the data for the null vales by reducing the required space in database.  The disadvantage is that there is more processing required querying the non-null values in the columns.  Microsoft recommends enabling SPARSE on columns where the space to be saved is at least 20%.

Not all data types can be enabled to use the SPARSE keyword these include:
  • Geography
  • Geometry
  • Image
  • Text
  • Timestamp
  • ntext
  • User Defined Data Types

 

http://technet.microsoft.com/en-us/library/cc280604.aspx

Leave a Reply

Your email address will not be published. Required fields are marked *