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

■ it is worth mentioning that there are other design solutions besides wide tables that can help in such situations.
You may consider storing different document types in separate tables with another table used to store common document
attributes. alternatively, you can use xMl to store some of the attributes or unpivot them in another name/value pairs
table. every approach has its pros and cons based on business and functional requirements.

Note

personally, i prefer to avoid wide table design. Working with semistructured data often has the requirement to change
schema on the fly by adding or removing attributes to the data. implementations based on wide tables lead to frequent
alterations, which introduces considerable overhead in both the database development and administrative areas.

There is still a limitation of a maximum of 1,024 nonsparse columns per table. Moreover, the in-row part of the
row must not exceed 8,060 bytes.
Managing a large number of sparse columns in the code can become cumbersome. As a workaround, SQL
Server allows you to define a designated column called COLUMN_SET . Think about COLUMN_SET column as an untyped
calculated XML column that contains information about NOT NULL sparse columns from a row.
The COLUMN_SET column changes the behavior of select * operation. When it is specified, SQL Server does not
include individual sparse columns in the result set, returning COLUMN_SET column instead. Moreover, if you add new
sparse columns to a table, they would appear in the result set.
Listing 4-28 illustrates an example of this. The code creates two tables with sparse columns—one with
COLUMN_SET —and it populates them with the same data.

Listing 4-28. Sparse columns: COLUMN_SET—tables creation

create table dbo.SparseDemo
(
ID int not null,
Col1 int sparse,
Col2 varchar(32) sparse,
Col3 int sparse
);

create table dbo.ColumnSetDemo
(
ID int not null,
Col1 int sparse,
Col2 varchar(32) sparse,
Col3 int sparse,
SparseColumns xml column_set for all_sparse_columns
);

insert into dbo.SparseDemo(ID,Col1) values(1,1);
insert into dbo.SparseDemo(ID,Col3) values(2,2);
insert into dbo.SparseDemo(ID,Col1,Col2) values(3,3,'Col2');

insert into dbo.ColumnSetDemo(ID,Col1,Col2,Col3)
select ID,Col1,Col2,Col3 from dbo.SparseDemo;

As a next step, let's select data from those tables using the select * operator as shown in Listing 4-29.