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.