BANGALORE, INDIA: SQL Server 2008 comes with quite a few new features that have more stress on query performance such as filtered index. In this article, we will see how exactly filter indexes help SQL developers and DBAs and also its advantages when compared to full table non-clustered indexes.

Filtered index is basically a non-clustered index and as suggested by the name allows us to filter the subset of a data with a where condition. For example, say you have a table with around 90% Null values and 10% non-null values for a particular column having frequent queries on that column. In a large dataset, even though you might be able to improve the performance with the help of a full table non-clustered index, you might still not be able to improve query performance significantly as the dataset gets updated in future.

Now, this is where filtered index comes as a great help.
Following points should be remembered while going for a filtered index:

• Can be created on columns of a particular table with a non-clustered index
• Cannot be used with Where clause having complex logic such a 'Like' clause
• Cannot be created on full text indexes
• Cannot be created on a view but can be defined on a table on which the view is based

The general syntax for a Filtered Index is:

CREATE NONCLUSTERED INDEX [index_name]
ON [table_name] ([column_list])
Include ([column_list])
WHERE [Filter_Predicate]