Using Column Sets with Sparse Columns

Although the cost of storage is comparatively low, we still need to consider saving space by using various techniques such as compression and archiving.  When you think of space savings the first thing you think of is the file system however space savings can be applied to the databases as well.  When we create a database we ensure that the data files are created with the appropriate size and growth rate. We periodically analyze the sizes of our databases and perform shrinking operations. We may be performing these tasks for different purposes, but in a way, these tasks help us to ensure optimal storage for our databases. Microsoft SQL Server provides us various techniques for reducing the space used by a database. SQL Server 2008 introduces a technique for addressing nullable columns that provides optimal storage for nullable columns. This new feature in SQL Server 2008 is known as SPARSE columns. This article does not cover much about the features of SPARSE columns, but it does cover the usage of sparse columns with Column Sets, and the things you need to know and consider when using them.

This article contains:
What is a SPARSE Column?
What is a Column Set?
Inserting and updating data in a column set
Tracking modifications with triggers
Applying security on a column set

What is a Sparse Column?
The sparse column is an ordinary column just like other columns but it reduces the storage requirement for null values. A nullable column can be made as sparse column by adding the SPARSE keyword when the table is created or altered. Once the column is a SPARSE column SQL Server will not allocate space for null values. Note that in using this feature it adds an overhead for data retrieval of non-null values. Therefore you need to carefully apply this feature for columns by calculating the space that can be saved. It is recommended to make the column a SPARSE column only if the space that could be saved is at least 20 to 40 percent. BOL provides a table that contains the required null percentages in the columns for each data type in order to make the columns a SPARSE column. Refer to the article "Using Sparse Columns" in BOL for more info.

What is a Column Set?
The Column set is a column that represents all the sparse columns added to the table as a xml-typed column. It is not physically available in the table, it is just like a calculated column but it allows you to make modifications on it. It is recommended to have a column set only if you have lot of sparse columns in you table because it speeds up the modifications and the retrieval if the column set is used instead of the sparse columns individually. You can get more information about column sets if you refer to "Using Column Set" in BOL.

The following code shows the way of creating a column set for a table.

Code 1:  Creating a table with sparse columns and a column set.

CREATE TABLE [dbo].[Customers]
(
    [Id] int PRIMARY KEY,
    [FirstName] varchar(50) NOT NULL,
    [LastName] varchar(50) NOT NULL,
    [Gender] bit SPARSE NULL, -- 1 = male, 2 = female
    [Telephone] varchar(15) SPARSE NULL,
    [MonthlyIncome] money SPARSE NULL,
    [Comments] varchar(1000) SPARSE NULL
    [AllSparseColumns] xml COLUMN_SET FOR ALL_SPARSE_COLUMNS
)

I have added the SPARSE keywords for all nullable columns, but as I mentioned earlier, the percentage of null values should be analyzed before making them sparse columns. Note that you need to add this column when you create the table. SQL Server does not allow you to add a column set by using the ALTER TABLE statement, but it does allows you to have a column set column without having sparse columns. The columns that are added later as sparse columns will be available with the added column set. See the code given below;

Code 2:  Creating a table with a column set, without making any columns as sparse columns.

-- adding column set without sparse columns
CREATE TABLE [dbo].[Customers_1]
(
    [Id] int PRIMARY KEY,
    [FirstName] varchar(50) NOT NULL,
    [LastName] varchar(50) NOT NULL,
    [Gender] bit NULL, -- 1 = male, 2 = female
    [Telephone] varchar(15) NULL,
    [MonthlyIncome] money NULL,
    [Comments] varchar(1000) NULL,
    [AllSparseColumns] xml COLUMN_SET FOR ALL_SPARSE_COLUMNS
)

-- inserting a record
INSERT INTO dbo.Customers_1
    ([Id], [FirstName], [LastName], [Gender], [Telephone], [MonthlyIncome], [Comments])
VALUES
    (1, 'Dinesh', 'Priyankara', 1, '777395871', 20000, 'no comments')

-- this returns null
SELECT AllSparseColumns FROM dbo.Customers_1

-- Make the Gender column as a sparse column
ALTER TABLE [dbo].[Customers_1]
    ALTER COLUMN [Gender] bit SPARSE NULL
GO

-- Make the Telephone column as a sparse column
ALTER TABLE [dbo].[Customers_1]
    ALTER COLUMN [Telephone] varchar(15) SPARSE NULL

-- Now it returns values of sparse columns as a xml
SELECT AllSparseColumns FROM dbo.Customers_1