Reframing to Overcome Filtered Index Limitations

I’m continuing on with the Blogger’s challenge in this post.

Turning the Problem Around

In the last post, I wrote about limitations in filtered indexes. I proposed that my table had lots of data with NULL or blank spaces in the gender column, and I wanted to avoid indexing those rows. I tried this code

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

However, that failed. I can’t use two sets of criteria in the filtered index. But I can use one, so I need to re-frame the problem.

If I look at the data, I have four choices: ‘M’, ‘F’, NULL, ‘ ‘. I can group two of those choices together, looking for a positive (matching) set of data rather than a negative (non-matching set. The easy way to do that is with an IN clause.

CREATE INDEX Users_Gender_Filtered2
 ON Users (gender)
  WHERE gender IN ('M', 'F');

This works, and I’ve got a filtered index. In the real world, I’d actually drop the first index (gender is not null), and only go with this one.


A few places I used to research this post.

Quick and Easy Blogging

This post occurred to me as I was writing the other post. I almost added a note on turning the index around, but realized this is a separate topic, and it makes a nice, second post. This post required < 10 minutes.

This post continues the April Blogger Challenge is from Ed Leighton-Dick, aimed at new bloggers, but anyone is welcome. I’m trying to motivate and cheer people on.

About way0utwest

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

12 Responses to Reframing to Overcome Filtered Index Limitations

  1. Dwain Camps says:

    Out of curiosity, is there a reason you couldn’t or wouldn’t want to do it like this?

    CREATE INDEX Users_Gender_Filtered2
    ON Users (gender)
    WHERE NULLIF(gender, ”) IS NOT NULL;

    • way0utwest says:

      No reason. Is there a performance issue or logic issue? I was thinking of specifying a domain here, though obviously that could change. I’d say that I think the NULLIF(gender,”) IS NOT NULL is certainly harder to read.

  2. Thomas Franz says:

    you can’t use NULLIF in the WHERE clause of a filtered index (and even if it would be possible, I doubt that the query optimizer would be able to use it except you would always query where Nullif(gender, ”) = ‘M’)

  3. Vijay Govindan says:

    Hi Steve, enjoying your series. We have deployed a few FI’s with multiple criteria. I think it might have to do with IS NOT NULL in the Where clause. I am presenting next week at my company on how we are using FI’s in our ETL. Vijay

  4. Don Simpson says:

    Are there any issues with doing it this way (this assumes domain of ‘M’, ‘F’, NULL, ‘ ‘):

    CREATE INDEX Users_Gender_Filtered2
    ON Users (gender)
    WHERE gender > ‘ ‘;

    • Vijay Govindan says:

      Don, that seems it would be valid. The key with FI’s is to make sure the Query Optimizer will consistently use it. Which ever way enables the Optimizer would be the way I would choose.

    • way0utwest says:

      I agree with Vijay. There isn’t any issue unless it’s an index that isn’t used. If you query on gender regularly, but you have lots of NULLs or some value you can eliminate with the filter, this dramatically reduces the cost of using the index. For example, if I had a username column in there I use often, I might choose to add this as a column along with gender.

      I don’t like the > ” because it allows lots of other values in there. For example, if I don’t want the index full of “bad” values outside the domain because I have bad data, I don’t want ‘A’, ‘B’, being stored in the index. In this case I was showing an example of just limiting the index to specific values to be as small as possible.

Comments are closed.