Blog Post

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.

References

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.

Filed under: Blog Tagged: blogging, indexing, syndicated, T-SQL

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating