After a brief blog delay we're back and continuing our example from last time around sparse columns and their uses in SQL Server 2008.

Today I want to talk about filtered indexes and how you can use them with your sparse columns.  Continuing with the dbo.ProductSparse table, I've updated a percentage of the table with values and left a small amount NULL.  Here's the quick code:

--Update some rows but leave NULLs
UPDATE dbo.ProductSparse
SET Size = 1
WHERE ProductID BETWEEN 0 AND 2000;

UPDATE dbo.ProductSparse
SET Size = 2
WHERE ProductID BETWEEN 2001 AND 4000;

UPDATE dbo.ProductSparse
SET Size = 3
WHERE ProductID BETWEEN 4001 AND 6000;

If we execute the query below on dbo.ProductSparse we will see an index scan and some IO:

SELECT ProductID, [Description], Size
FROM dbo.ProductSparse
WHERE Size = 1;

Table 'ProductSparse'. Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

So let's add an index filtering out NULL values for the Size column.  The only syntax difference for filtered indexes is the inclusion of a WHERE clause at the end of the declaration.

CREATE INDEX IDX_ProductSparse_Size ON dbo.ProductSparse (Size) WHERE Size > 0 AND Size < 3;

Now if we execute the same query as before we will see an index seek.

We've now modeled desperate data and given ourselves the ability to query it in a palatable manner.

To see the layout of the data with the addition of our filtered index we can query the sys.dm_db_partition_stats DMV:

SELECT i.name
    ,s.in_row_data_page_count
    ,s.in_row_reserved_page_count
    ,s.in_row_used_page_count
    ,s.row_count
FROM sys.dm_db_partition_stats AS s
    INNER JOIN sys.indexes AS i ON s.index_id = i.index_id AND s.object_id = i.object_id
WHERE s.object_id = OBJECT_ID('dbo.ProductSparse', 'U');

Here's our output:

So we can see some nice benefits of filtered indexes coupled with sparse columns.  In prior versions of SQL Server I would have had to implement an indexed view to get this same functionality.  Now I can maintain a single structure and have none of the additional overhead associated with an indexed view.  Good times for everyone. :)

-W