A Little About Index Sort Direction In SQL Server
In this video, I delve into an often-overlooked aspect of SQL Server indexes: sort direction. You'll learn about the importance of specifying ascending or descending order when creating indexes and how it can significantly impact query performance, especially for windowing functions. I walk you through a practical example in SQL Server Management Studio, demonstrating how to create an index that matches the sort order of your windowing function to drastically reduce execution time-from 11 seconds down to just under 3 seconds. Along the way, I also touch on the benefits of using batch mode for even further optimization, and share some behind-the-scenes thoughts on Microsoft's development process, which can sometimes leave a lot to be desired.
CHAPTERS
00:00:00 - Introduction and Setup
00:02:35 - The Importance of Indexes for Windowing Functions
00:06:07 - Creating an Index to Match the Sort Order
00:08:41 - Performance Improvement with Indexed Windowing Function
00:10:03 - Using Batch Mode for Better Performance
00:12:21 - Conclusion and Final Thoughts
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━