Filtered indexes can be tricky. Just getting your queries to use the filtered index can be a real pain. Parameterization may mean it’s “unsafe” to use the filtered index. The optimizer might want the filtered column in your key or include column list when it doesn’t seem like it should have to be there. The weather might be cloudy.
But there’s one type of filtered index that everyone can love for a couple different reasons: IS NOT NULL.
“IS NOT NULL” filter for a selective query…
Here’s an example index. We’re using the StackOverflow sample database and creating the index only on Posts which are closed (a small subset):
CREATE INDEX ix_Posts_Score_ClosedDate_INCLUDES_FILTERED on dbo.Posts
(Score, FavoriteCount, ClosedDate)
INCLUDE (Id, Title, Tags, CommentCount, OwnerUserId, LastEditDate, LastEditorDisplayName)
WHERE (ClosedDate IS NOT NULL);
Here’s a query looking for Posts with a ClosedDate in a given range. Note that the query does NOT say “ClosedDate is NOT NULL”, it’s just specifying a value range:
CREATE PROCEDURE #ClosedPostsByScoreAndFavorites
FROM dbo.Posts as p
JOIN dbo.Users as u on p.OwnerUserId=u.Id
p.ClosedDate between @Start and @End
and p.Score > @Score
and p.FavoriteCount > @FavoriteCount
We give the query a run and look at the plan….
EXEC #ClosedPostsByScoreAndFavorites @Start='2011-01-01', @End='2014-01-01', @Score=2, @FavoriteCount=2;
Woooo, it matches my index!
And now for my next trick… what if you need to guarantee uniqueness for a column that allows NULLS?
We’re entering controversial territory here, so I’m going to be careful. SQL Server treats NULL as a value. This is a really big deal to some people, and I totally understand if you want to vent in the comments about how that’s a terrible thing and it shouldn’t be that way, and SQL Server stole your pickup truck. But it’s the SQL Server we live in.
Treating NULLs as a value means that I can’t create a unique index or a unique constraint on a column that allows NULLs if it has more than one row with NULL in it. More than one NULL means I have duplicates.
A filtered unique index can get you around the problem, like this:
CREATE UNIQUE INDEX uq_UsersAllowNulls_DisplayName on dbo.UsersAllowNulls ( DisplayName )
WHERE DisplayName IS NOT NULL;
Yay for indexes!
We’ve got tons of material on indexes, start reading more over here.