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.
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.