SPARSE data type in SQL Server 2008
Sparse data type in SQL Server 2008
By : Kasim Wirama, MCDBA
This article will introduce another new data type in SQL Server 2008, SPARSE type. In my sight, this sparse type is similar to numeric type with NULL. I am surprised that a conditional index could be created upon the sparse column. A conditional index? It looks weird? How come an index could be conditional? And how the index is useful in some query context? Let’s take a look a bit further by looking at the following example.
CREATE TABLE myTable (col1 INT SPARSE);
CREATE INDEX mySparseIndex on myTable (col1) WHERE col1 = 1;
INSERT INTO myTable VALUES (1);
INSERT INTO myTable VALUES (2);
INSERT INTO myTable VALUES (NULL);
SELECT * FROM myTable WHERE col1 = 1; -- the index is used, non 1 value will not use this index.
This index will be usefull when col1 is dominated by other value, and 1 value is a minority, so it will give a hand for optimizer to generate a good plan.
What happened in database engine level? I couldn’t answer your deep question for this time, another SQL Server 2008 documentation will give me more insight about internal work in core database engine.