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.
- https://msdn.microsoft.com/en-us/library/cc280372.aspx
- https://www.simple-talk.com/sql/performance/introduction-to-sql-server-filtered-indexes/
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.
Voice of the DBA 
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;
LikeLike
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.
LikeLike
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’)
LikeLike
Thanks, Thomas. Didn’t realize that wasn’t legal, and sure enough, it’s not.
LikeLike
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
LikeLike
Thanks and you should write something up for SQLServerCentral on how this is working.
LikeLike
Hi Steve, you are welcome. I would love to write something up at SQLServerCentral and give back. How would I go about doing that?
LikeLike
Vijay,
You can submit a draft here: http://www.sqlservercentral.com/Contributions/Home (or send one to sjones at sqlservercentral period com)
LikeLike
Great, let me see what I can put together and I will forward it on to you for review.
LikeLike
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 > ‘ ‘;
LikeLike
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.
LikeLike
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.
LikeLike