In my Are you ready for SQL Server 2012 or are you still partying like it is 1999? post, I wrote about how you should start using SQL Server 2005 and SQL Server 2008 functionality now in order to prepare for SQL Server 2012. I still see tons of code that is written in the pre 2005 style and people still keep using those functions, procs and statements even though SQL Server 2005 and 2008 have much better functionality.

Today we are going to take a look at Filtered Indexes. A filtered index is an optimized nonclustered index, the filtered index is based on a WHERE clause, thus this index will cover your query if your query has the same WHERE clause

We are going to create a table and populate it with 52048 rows, 50000 will be null and the rest will be numbered between 0 and 2047.

Now we will create an identical table

We are adding a simple non clustered index on the table

We are adding a filtered index on the second table, notice that the create statement is almost identical, all we have added really is the WHERE clause

Now, let's select all rows where the id is not null, you will get back 2048 rows for each query

Here is what the execution plan looks like

As you can see the filtered index performs better. What if we select where it is null instead of not null?

That plan looks a little better also.

Take a look at IO, even the reads look better

(50000 row(s) affected)
Table 'TestFiltered'. Scan count 1, logical reads 114

(50000 row(s) affected)
Table 'TestFiltered2'. Scan count 1, logical reads 84

How much space does a filtered index like this use compared to a regular index. Look at the storage difference

name		rows	reserved	data	index_size	unused
-------------   ------  ----------  -------     -----------       -----
TestFiltered	52048   1744 KB	     672 KB	952 KB		120 KB
TestFiltered2	52048    768 KB	     672 KB	 64 KB		32 KB

As you can see the table with the filtered index is much smaller in size, the index is 14 times smaller

An index like this is very useful when you have columns that are nullable and a large percentage of the values are NULL, when you run a report in general you want to see the values, a filtered index will be very useful with that.

Here are some advantages that filtered indexes have over regular indexes according to Books On Line

Filtered indexes can provide the following advantages over full-table indexes:

Improved query performance and plan quality

A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.

Reduced index maintenance costs

An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.

Reduced index storage costs

Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.

Look up filtered indexes in Book On Line and play around with them to see if they will be beneficial in your database

That is it for today's post, come back tomorrow to learn about indexes with included columns.