MSDN article

Tables that use sparse columns can designate a column set to return all sparse columns in the table. A column set is an untyped XML representation that combines all sparse columns of a table into a structured output.
A column set is not physically stored in the table.
A column set is updatable.
A column set should be used when the number of columns in a table is large and operating on them individually is cumbersome.
Using column sets may improve select and insert operations performance, but not when many indexes exists on the table.
Example:


-- Often patient information is missing in the intermediary
-- data processing engine. It is added in the last processing
-- stage in LIS. This is the reason this table has many nullable columns.
Create table Patient
(
Id int identity(1,1) not null primary key clustered,
LabId nvarchar(100) not null,
InsuranceId nvarchar(100) SPARSE null,
FirstName nvarchar(100) SPARSE NULL,
LastName nvarchar(100) SPARSE null,
DateOfBirth datetime SPARSE null,
Gender int SPARSE null,
DoctorLabId nvarchar(100) SPARSE null,
AdmissionStatus int SPARSE null,
AdmissionDate datetime SPARSE null,
Diagnosis nvarchar(100) SPARSE null,
Comment nvarchar(200) SPARSE null,
ColSet XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)

When designing a table, be aware a colum set cannot be added to a table if that table already contains sparse columns. When you have a table with sparse columns and you realize you need a column set, you have to create a new table and migrate existing data.
On the bright side, if a table has a column set and a new sparse column is added, the new column is automatically added to the column set.
Only one column set is allowed per table.
A select * query will only return the column set and it will not return the individual sparse columns.

Insert data in the Patient table and query it.

insert Patient( LabId, FirstName, LastName, DateOfBirth, Gender, Comment)
values
( '20001', 'Hans', 'Mueller', '1950-12-01', 1, 'Under anemia medication'),
( '20002', null, null, '1934-01-21', 1, null),
( 'M2322', 'Karl', 'Lund', '1980-06-04', 1, null ),
( 'A21211', 'Mary', 'Smith', '1983-02-02', 2, null )

Get all data as xml.

select ColSet from Patient
returns

<FirstName>Hans</FirstName><LastName>Mueller</LastName><DateOfBirth>1950-12-01T00:00:00</DateOfBirth><Gender>1</Gender><Comment>Under anemia medication</Comment>

<DateOfBirth>1934-01-21T00:00:00</DateOfBirth><Gender>1</Gender>

<FirstName>Karl</FirstName><LastName>Lund</LastName><DateOfBirth>1980-06-04T00:00:00</DateOfBirth><Gender>1</Gender>

<FirstName>Mary</FirstName><LastName>Smith</LastName><DateOfBirth>1983-02-02T00:00:00</DateOfBirth><Gender>2</Gender>

Insert values:

insert Patient(LabId,ColSet)
values
(
'2002222',
'<LastName>Rice</LastName><Comment>VIP!</Comment>'
)

Advertisements