Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 250

F A sparse column cannot be a part of a user-defined table such as Table Variables
and Table-Valued Parameters ( TVP )
F The sparse columns cannot be added to compressed tables or allowed to
be compressed

How to do it...

The following steps need to be completed in order to implement sparse columns and
wide table features;

1. To define a sparse column, we need to add a sparse storage attribute using CREATE
TABLE or ALTER TABLE statements.

2. Let us create a new table with sparse columns by using the following TSQL statement:

CREATE TABLE dbo.SparseDemo
(ProductID INT NOT NULL PRIMARY KEY,
ProductName NVARCHAR(50) NOT NULL,
ProductColor NVARCHAR(18) SPARSE NULL,
ProductWeight DECIMAL(12,2) SPARSE NULL,
SellDate DATETIME SPARSE NULL,
ProductDesc XML COLUMN_SET FOR ALL_SPARSE_COLUMNS)

3. Next, insert the data from the AdventureWorks2008R2 database Production .

Product table:
INSERT INTO [dbo].[SparseDemo]
([ProductID]
,[ProductName]
,[ProductColor]
,[ProductWeight]
,[SellDate]
)
SELECT ProductID, Name, Color, Weight, SellEndDate
FROM Production.Product

4. The data that is inserted into the table can be queried by using the specified sparse
columns as follows:
SELECT ProductID, ProductName, ProductColor,ProductWeight,SellDate
from dbo.SparseDemo

5. If we use SELECT * FROM in a query, then only the column set is returned as an XML

column instead of each column. This is advantageous to reduce rendering time and
obtain populated data only; however, it may miss out the column name specifically.

6. In order to return the result as relational column, the sparse columns must be

explicitly listed in the query.