0

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?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
5
  • Cool, thanks, yea I actually was hoping someone would comment on the variable string concatenation in the LIKE filter. I noticed hard-coding it does result in measurably faster execution time. Any ideas on how I can re-write the query to avoid the dynamic seek plan without recompiling it every time? (I guess I could generate the query with dynamic SQL and if the same character is inputted then it'll re-use the same cached plan, so slightly better.)
    – J.D.
    Jan 16, 2020 at 22:37
  • 1
    @J.D. Great question! If you really only need to match the first letter, you should create a computed column that is just the first letter of that column, and then index it. Then you can do an equality predicate instead of LIKE and you'll get normal seeks. I'll add an example in a bit.
    – Josh Darnell
    Jan 17, 2020 at 3:24
  • Cool, yea that makes sense. I've done stuff like that before but just wasn't thinking of it in this case, that's a great idea, thank you!
    – J.D.
    Jan 17, 2020 at 13:16
  • Btw, I forget with computed columns, if I wanted to index it, do I need to make it a persisted computed column?
    – J.D.
    Jan 17, 2020 at 13:30
  • 1
    You're welcome, always glad to help a fellow @J.D. 😁 And no, you don't need to persist the computed column in order to index it. Then it will be persisted in the index, but not in the base table.
    – Josh Darnell
    Jan 17, 2020 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.