Filtered Indexes on SQL Server 2008

What is a ‘filtered’ index?

A filtered index is basically a non-clustered index that is created on a subset of the data in a table defined by some query criteria(AKA the filter). Please note that I wrote non-clustered for a reason. You cannot create a filter on a clustered index.

Why is this cool?

It’s cool because it gives you another way in which you can positively influence performance on your queries. If you remember back in the days of Windows 2000, you will remember that one of the main constraints on performance in the system used to be CPU spikes. Since 2005 cam along that has shifted to where the main problem is almost always disk I/O. So anything that we do to limit disk I/O will reflect positively in our environment in terms of performance.

Filtered indexes are a way in which to create an index not on an entire set of data but rather a smaller subset that is utilized by the majority of the queries on the system. Since we are filtering the data placed in the index then this means that the indexes will be smaller. Smaller indexes mean that there is less data manipulation needed in order to traverse the index to get to the data you want. Less data manipulation leads to less disk I/O which in turns equals ‘DBA BLISS’.

How do you create a filtered index?

Creating a filtered index is entirely too easy. All you have to do is specify a filter constraint on the CREATE INDEX statement

CREATE INDEX <index name>

ON <table name>(column name)

WHERE <filter criteria>

So lets go ahead and create a filtered index and a non-filtered index on a column for DEPT_NUM in our Project REAL sample database. Then lets string together a short query to show what the results are

create index idx_filteredtest
ON SRS_SALES_TRANS_FULL(DEPT_NUM)
WHERE DEPT_NUM=’06’

Here you can see that we just created an index as we normally would except we placed a WHERE clause at the end. This is the filter that the index will be created on. Now let’s create the common index on the column as well.

create index idx_regularindex
ON SRS_SALES_TRANS_FULL(DEPT_NUM)

Nothing out of the ordinary there. Now lets look at the sys.partitions table and the sys.indexes table to get a view of how many rows each index contains.

select i.name, p.rows, i.filter_definition
from sys.partitions p inner join
sys.indexes i on p.object_id=i.object_id and p.index_id=i.index_id
WHERE OBJECT_NAME(i.object_id) = ‘SRS_SALES_TRANS_FULL’

And the results ….

As you can see, even with this relatively small table the filtered index is at a big advantage in terms of size. Imagine this now on a table that contained something like millions of stock market entries or millions of orders of widgets. I think that you will agree that while this may not be one of the flashiest additions to SQL Server it could be one of the most useful in terms of performance.

Cheers,
AJ