SQL Server 2008 has a very nice new feature that enables a partial index to be created based on a specific where clause.
I have recently used this as an solution checking a table that contained an isSent flag. by creating a non-clustered index on the condition of ‘isSent’ = 0 (where 0 means not sent) I was able to reduce the scans on the clustered index. previously this index was scanned every time an email delivery system was polled. This lead to some considerable savings.
The three examples below are taken from the same query that was run with different mechanisms. Each Table is identical with data having 96% of rows having the ‘isSent’ flag set to 1. there were approx. 200000 rows in the table. In each example only the non clustered indexes changed.
the first table emailqueuev1 had no nonclustered index index so the table was scanned on each search, the second had a non-clustered index created on the ‘isSent’ field and contained a reference to each row in the clustered index. The third contains a filtered non-clustered index this only contained rows from the clustered index that fitted the condition ‘isSent’ = 0
The following queries were then run on the seperate tables.
Select * from emailqueuev1 where isSent = 0Select * from emailqueuev2 where isSent = 0 Select * from emailqueuev3 where isSent = 0
As you can see the first index scans the entire table due to the size of this table it means there is a query cost 20 times the other 2 methods. The second seeks the non-clustered index and the third also seeks the nonclustered index both of these methods are fairly light weight. There is a small improvement between the second and third queries in processing. This improvement will only get better in time as the size of the clustered index increases
The major saving is from storage space. The second index is comprised of 203 pages of data. This could increase dramatically in size if you created included columns within the index
The third Index is comprised though of only 2 pages (16kb) of data.
The beauty of the filtered index is once the isSent flag is changed the reference is removed from this small index. So it never gets too large.