SQL Server 2008 Column Sets
We're back on the 2008 topics here at the W, talking about column sets. Column sets extend sparse columns allowing you another mechanism to query your desperate data.
A column set is implemented as an XML column on your table. For this example I will use the same table in my earlier sparse column post. Here is the syntax:
IF OBJECT_ID('dbo.ProductSparse', 'U') IS NOT NULL
DROP TABLE dbo.ProductSparse;
CREATE TABLE dbo.ProductSparse
(
ProductID INT IDENTITY(1,1)
,[Description] VARCHAR(100) NOT NULL
,Col02 INT SPARSE NULL
,Col03 INT SPARSE NULL
,Col04 INT SPARSE NULL
,Col05 INT SPARSE NULL
,ProductSparseSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
);
Now we will insert some data. The script below will create 4000 rows in our table spread across the various sparse columns.
--Insert some data
INSERT dbo.ProductSparse ([Description], Col02) VALUES ('aaa', 1);
INSERT dbo.ProductSparse ([Description], Col03) VALUES ('bbb', 2);
INSERT dbo.ProductSparse ([Description], Col04) VALUES ('ccc', 3);
INSERT dbo.ProductSparse ([Description]) VALUES ('ddd');
GO 1000
If we query the table with "SELECT *" we will get back the column set only, all our SPARSE columns will be obscured. Here's a snippet of our table:
SELECT *
FROM dbo.ProductSparse;
Another nice touch of the column set is that we can use our normal means of dealing with XML data for this column. We can insert or update data as easily with the column set XML as we can the actual column(s). One caveat -- if you do use the XML for updating sparse columns any column not referenced in the update will be set to NULL.
While this feature was implemented mainly to support future SharePoint functionality there are many business cases in the OLTP world that can benefit. Product catalogs, census style information or anything else where desperate data exists can be more successfully modeled with sparse columns.