The April Blogger Challenge is from Ed Leighton-Dick and aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.
Why Filtered Indexes
I haven’t used filtered indexes much in my career as they didn’t exist while I was a full time SQL developer and DBA. They appeared in SQL Server 2008, and are a very useful feature in certain circumstances.
Imagine I have a simple table:
I want to add some indexes that look for specific fields since those are times I am limiting searching in the table. For example, suppose that gender is an optional entry by users. I find that I often have NULL in this column, so I create a filtered index.
I’ll do that as follows:
CREATE INDEX Users_Gender_Filtered ON Users (gender) WHERE gender IS NOT NULL;
This is the same type of index statement we normally use, but we’ve added a WHERE clause to the statement. This means that only those rows with a non-NULL value in the gender column will be included in this index.
Why Use Filtered Indexes?
There are some reasons given in the BOL article below, but the main reason to do this is performance. I could easily add an index using gender that isn’t filtered, but if I had 1.5mm rows in my table and 1.4mm of them have NULL here, then do I want to scan those 1.4mm rows if a query wants a specific gender? I don’t.
Indexes take space, which means more space on disk, more backup space, and more time for backups. However they also impact performance when we read the index, or we update a column used in an index. Each of those operations requires either a read or write of the index. If I can reduce the reads and writes to indexes, then I can improve performance dramatically.
I would urge you to look at filtered indexes whenever you have data where a large number of rows are not going to match a query. In these cases, you can use a filtered index that is highly selective.
A few places I used to research this post.
Quick and Easy Blogging
This post occurred to me while I was writing some code and was the first post I thought of for the challenge. However I realized I could start with the other posts, and so I wrote those posts first. This one took about 10 minutes to read through limits on filtered indexes, try 4-5 experiments to get the code to work, and write the post.
Look for the other posts in the April challenge.