Sparse Columns in SQL Server 2008

A coworker sent this link to me today. It’s an interesting feature for SQL Server 2008 which allows you to save space for nullable columns.

Here’s some of the basic info:

Sparse columns are ordinary columns that have an optimized storage for null values. Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent. Sparse columns and column sets are defined by using the CREATE TABLE or ALTER TABLE statements.

Sparse columns can be used with column sets and filtered indexes:

  • Column sets

    INSERT, UPDATE, and DELETE statements can reference the sparse columns by name. However, you can also view and work with all the sparse columns of a table that are combined into a single XML column. This column is called a column set. For more information about column sets, see Using Column Sets.

  • Filtered indexes

    Because sparse columns have many null-valued rows, they are especially appropriate for filtered indexes. A filtered index on a sparse column can index only the rows that have populated values. This creates a smaller and more efficient index. For more information, see Filtered Index Design Guidelines.

Sparse columns and filtered indexes enable applications, such as Windows SharePoint Services, to efficiently store and access a large number of user-defined properties by using SQL Server.

Sparse columns have the following characteristics:

  • The SQL Server Database Engine uses the SPARSE keyword in a column definition to optimize the storage of values in that column. Therefore, when the column value is NULL for any row in the table, the value requires no storage.
  • Catalog views for a table that has sparse columns are the same as for a typical table. The sys.columns catalog view contains a row for each column in the table and includes a column set if one is defined.
  • Sparse columns are a property of the storage layer, rather than the logical table. Therefore a SELECT…INTO statement does not copy over the sparse column property into a new table.
  • The COLUMNS_UPDATED function returns a varbinary value to indicate all the columns that were updated during a DML action. The bits that are returned by the COLUMNS_UPDATED function are as follows:
    • When a sparse column is explicitly updated, the corresponding bit for that sparse column is set to 1, and the bit for the column set is set to 1.
    • When a column set is explicitly updated, the bit for the column set is set to 1, and the bits for all the sparse columns in that table are set to 1.
    • For insert operations, all bits are set to 1.

    For more information about columns sets, see Using Column Sets.




How to Set SQL Variables in Dynamic SQL Text

The following example shows how to use variables for input and output for execution of sql text.  This is handy for stored procedures that use sp_executesql for executing query text against a database.

-- Declare Input/Output Variables
Declare @vc_sql 		nvarchar(4000)	-- MUST BE NVARCHAR
	,@vc_input_Name  	varchar(200)
	,@sdt_output_BirthDay 	Smalldatetime

select @vc_sql = '
Select @sdt_output_BirthDay  = Employee.Birthday
From  dbo.Employee (NoLock)
where  Employee.Name >= ''@vc_input_Name'''

exec sp_executesql 	@vc_sql
			,N'@vc_input_Name varchar(200), @sdt_output_BirthDay Smalldatetime output'
			,@sdt_output_BirthDay out