Tune the 'Peak Years' Procedure - SQLChallenge (52 minutes)

Ready to tune some TSQL? In this challenge, you’re given a database to restore. You’ll create two indexes and a constraint in the database– and you aren’t allowed to change those.

Your mission, if you choose to accept it, is to tune the TSQL in a stored procedure so it can make the best use of the given indexes – and still returns the same results. You’re given two sample calls to the procedure, and your goal is to make it as fast as possible.

Scripts

Grab the scripts here: https://github.com/LitKnd/SQLWorkbooks/tree/main/query_writing_peak_year

Download the database backup

Download this compressed file, which contains a backup spread across four files: https://drive.google.com/file/d/1w0ZGZKHq4N7n6eyP5puu63MuSH3o_hWb/view?usp=sharing

Unzip the files to a directory where you can restore them to a SQL Server. This demo works on SQL Server 2017 or higher. Developer Edition is free from Microsoft for non-production environments.

Lessons

Setup and problem, including scripts (16 minutes)

Sample solutions (36 minutes)