Sql-server – Should a filtered index theoretically perform faster in this example

filtered-indexindex-tuningnonclustered-indexsql serversql-server-2016

I have a SuperheroMovies table which has all superhero movies, and has a column called "UniverseName" which has either the values "Marvel", "DC", "Capcom", "Unknown".
It also has columns called "MovieId" (primary key) and "MovieTitle".
(There are also other columsn in SuperheroMovies, which we can call OtherField1…OtherField25.)

The clustered index is on the primary key MovieId.
There's also a nonclustered index IX_MovieTitle on the table for the MovieTitle column.

This is the query that will be ran the most against the SuperheroMovies table:

DECLARE @MovieTitlePrefix VARCHAR(50) = 'F'; -- This can be any single character from A to Z

SELECT MovieId, MovieTitle, UniverseName, OtherField1, OtherField2, OtherField3, ROW_NUMBER() OVER (PARTITION BY UniverseName ORDER BY MovieTitle) AS SortId
FROM SuperheroMovies
WHERE MovieTitle LIKE @MovieTitlePrefix + '%'
    AND UniverseName <> 'Unknown'

If a nonclustered index IX_MovieTitle_FilteredOnUniverseName ON SuperheroMovies (MovieTitle) WHERE UniverseName <> 'Unknown' was created and used in the query instead of IX_MovieTitle, should it perform faster?

I would still need the filter on UniverseName <> 'Unknown' in the query with IX_MovieTitle_FilteredOnUniverseName to achieve the same result set, right?

Best Answer

It would be at least a little faster, and also use less disk space, depending on how many rows there are with UniverseName = 'Unknown'.

The original index would allow the query to seek to the correct movie title starting character, and apply a residual predicate to each row to make sure it's not from an 'Unknown' universe.

The filtered index would still seek to the movie title starting character. But it wouldn't have to apply the residual predicate (it already knows none of those rows will be present). And it also might have to read less data pages, if there are a lot of rows with UniverseName = 'Unknown'.

Also, you should probably include UniverseName in the index definition, for the reasons outlined here: Should the filtering column(s) always be in the keys / includes?

Sidebar: the variable with string concatenation will likely result in a dynamic seek plan, unless you include a RECOMPILE hint on the query. This may or may not matter for your use case, but is something to be aware of.

Related Question