This challenge comes in three possible levels of difficulty: choose one or work through them all!
Level 1: design the best disk based nonclustered rowstore index for the sample query– in this case, “best” is defined as reducing the number of logical reads as much as possible for the query. Design only one index without using any more advanced indexing features such as filters, views, etc. Make no schema changes to the table other than creating the single nonclustered index.
Level 2: use a more advanced feature to minimize the number of logical reads for the query. This may involve a schema change other than simply creating the index.
Level 3: use a second more advanced feature to minimize the number of logical reads for the query, and compare the pros and cons of this solution with what you designed in Level 2. This may involve a schema change other than simply creating the index.
This “challenge” query is a fast query that would not normally require customized indexing. The same principles from this exercise apply to larger tables as well.
Challenge difficulty
Level 1 is designed as a 200 level challenge. Levels 2 and 3 are designed as 300 level.
Scripts
Grab the scripts for this course at https://github.com/LitKnd/SQLWorkbooks/tree/main/index_one_year_wonders_sqlchallenge