April Blogger Challenge 4–Filtered Index Limitations

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.

Filtered Index Limits

I ran across a limitation recently with filtered indexes and was surprised. Apparently, you can’t make complex filters in your definitions.

In the last post, I wrote about creating a filtered index on gender, looking for non-null columns in a table. That code looked like this:

CREATE INDEX Users_Gender_Filtered
  ON Users (gender)
  WHERE gender IS NOT NULL;

However, what if I find that I have lots of NULL values, but also lots of spaces, ‘ ‘, in my table because this is an optional field, and the data entry code changed at some point. Neither of these rows actually helps me in finding the rows with an ‘M’ or a ‘F’.

I tried creating an index like this:

CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IS NOT NULL OR gender = ' ';

That didn’t work, so I tried parenthesis.

CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE (gender IS NOT NULL OR gender = ' ');

That didn’t work either. No matter how I moved parens around, they didn’t allow a complex (if two criteria are “complex”) filtered index.

This is a limitation of a filtered index right now. I’m not sure if it will be changed, though I would like it to be. There are other limitations, so read the docs carefully, and think about what might work well in your environment.

References

A few places I used to research this post.

Quick and Easy Blogging

This post occurred to me while I was writing some code. I mocked up a table in about 2 minutes, and then ran a quick search on the Internet. Reading a few links was about 10 minutes and then testing the code (including dropping the table and recreating it a few times) was less than 5 minutes. All told, I solidified some knowledge and completed this in about 20 minutes. I also have drafts and ideas from this post for 2 other posts that cover this same topic in a similar way.

Look for the other posts in the April challenge.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , , . Bookmark the permalink.

One Response to April Blogger Challenge 4–Filtered Index Limitations

  1. Pingback: SQL New Blogger Challenge Digest – Week 4 | The Rest is Just Code

Comments are closed.