Special Indexing and Storage Features - Pro SQL Server Internals - page 109

Listing 4-29. Sparse columns: COLUMN_SET—select *
select 'SparseDemo' as [Table], * from dbo.SparseDemo;
select 'ColumnSetDemo' as [Table], * from dbo.ColumnSetDemo;
Figure 4-20 shows the results. As you can see, when you select data from the second table, there are no individual
sparse columns in the result set.
Figure 4-20. Sparse columns: COLUMN_SET and select *
You can insert or update sparse columns through the COLUMN_SET column. Listing 4-30 shows an example of this,
and Figure 4-21 shows the result of the execution.
Listing 4-30. Sparse columns: Using COLUMN_SET to manipulate data
insert into dbo.ColumnSetDemo(ID, SparseColumns)
values(4, '<col1>4</col1><col2>Insert data through column_set</col2>');
update dbo.ColumnSetDemo
set SparseColumns = '<col2>Update data through column_set</col2>'
where ID = 3;
select ID, Col1, Col2, Col3 from dbo.ColumnSetDemo where ID in (3,4);
Figure 4-21. Sparse columns: Using COLUMN_SET to manipulate data
Working with sparse columns through COLUMN_SET can simplify development and database administration,
especially when the table schema is changing due to business or functional requirements.