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.