syntaxhelp.com Your online cheat sheet!

Creating a table with SPARSE columns and a COLUMN SET

IF OBJECT_ID('Items') IS NOT NULL
	DROP TABLE Items 
	
CREATE TABLE Items(
	ItemID INT,
	Color VARCHAR(20) SPARSE,
	Width NUMERIC(6,2) SPARSE,
	Height NUMERIC(6,2) SPARSE,
	Weight NUMERIC (10,3) SPARSE,
	Size VARCHAR(10) SPARSE,
	Volume NUMERIC(10,2) SPARSE,
	ItemInfo XML COLUMN_SET FOR ALL_SPARSE_COLUMNS
)
GO

Inserting some sparse data

INSERT INTO Items (ItemID, Color) SELECT 1, 'red'
INSERT INTO Items (ItemID, Width, Height) SELECT 2, 12.2, 12.2
INSERT INTO Items (ItemID, Size) SELECT 3, 'XL'

A SELECT * will return the column set data in XML

SELECT * FROM Items 
/*
ItemID      ItemInfo
----------- ------------------------------------------
1           red
2           12.2012.20
3           XL
4           GreenS
*/

However, you can query the columns separatelyl

SELECT ItemId, Color FROM Items
/*
ItemId      Color
----------- --------------------
1           red
2           NULL
3           NULL
4           Green
*/

SELECT ItemId, Width, Height, size FROM Items
/*
ItemId  Width  Height  size
------- ------ ------- ----------
1       NULL   NULL    NULL
2       12.20  12.20   NULL
3       NULL   NULL    XL
4       NULL   NULL    S
*/

You can also perform an INSERT using the COLUMN SET

INSERT INTO Items (ItemID, ItemInfo)
SELECT 4, 'SGreen'

SELECT ItemID, Size, Color 
FROM Items WHERE ItemID = 4
/*
ItemID      Size       Color
----------- ---------- --------------------
4           S          Green
*/