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 columns 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?