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.

CREATE TABLE TestFiltered(id INT)
GO

--insert numbers between 0 and 2047
INSERT TestFiltered
SELECT number
FROM master..spt_values
WHERE TYPE = 'p'


-- add 50000 NULLS
INSERT TestFiltered
SELECT TOP 50000 NULL FROM sys.sysobjects s1
CROSS JOIN sys.sysobjects s2
CROSS JOIN sys.sysobjects s3

Now we will create an identical table

SELECT * INTO TestFiltered2 FROM TestFiltered

We are adding a simple non clustered index on the table

CREATE NONCLUSTERED INDEX ix_TestFiltered ON TestFiltered(id)
GO

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

CREATE NONCLUSTERED INDEX ix_TestFiltered2 ON TestFiltered2(id)
WHERE id IS NOT NULL
GO

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

SELECT * FROM TestFiltered
WHERE id IS NOT NULL


SELECT * FROM TestFiltered2
WHERE id IS NOT NULL

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?

SELECT * FROM TestFiltered
WHERE id IS  NULL


SELECT * FROM TestFiltered2
WHERE id IS  NULL

That plan looks a little better also.

Take a look at IO, even the reads look better

SET STATISTICS IO ON
GO

SELECT * FROM TestFiltered
WHERE id IS  NULL


SELECT * FROM TestFiltered2
WHERE id IS  NULL

SET STATISTICS IO  OFF
GO

_(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

exec sp_spaceused 'TestFiltered'
exec sp_spaceused 'TestFiltered2'

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.