Indexing for Windowing Functions (45 minutes)

Get the best performance for your windowing functions.

Windowing functions give you great flexibility for analyzing data in SQL Server, but can be tricky to index.

In this course, you will:

  • Learn index design for windowing functions
  • Learn when batch mode may be important for window function performance
  • Compare the performance of Window Spool and Window Aggregate operators

Get the scripts

https://github.com/LitKnd/SQLWorkbooks/tree/main/indexing_for_windowing_functions

Each video in this course has English captions available, as well as a transcript below the video.

Lessons

Meet the challenge code (9 minutes)

Dig into the plan and test a nonclustered index (11 minutes)

What can an indexed view do? (5 minutes)

Bring in the nonclustered columnstore index! (8 minutes)

Hacking in the Window Aggregate operator with a rowstore nonclustered index (9 minutes)

Summary and recap (4 minutes)