Annoyances With Filtered Indexes And Computed Columns In SQL Server
In this video, I delve into some of my pet peeves when it comes to filtered indexes and computed columns in SQL Server. Specifically, I highlight how the query optimizer's inability to use inverse logic with these features can lead to suboptimal performance, especially on larger tables where filtering is crucial. For instance, creating a filtered index based on 'isDeleted = 0' works fine when you phrase it as such, but using inverse logic like 'not one equals isDeleted' results in SQL Server ignoring the index and performing a full scan instead. This behavior can be particularly frustrating for developers who are trying to optimize their queries. Additionally, I discuss the challenges with computed columns, where even non-persisted columns can cause issues when the column order changes in your query, leading to full table scans despite having an ideal index in place. These annoyances underscore the importance of careful planning and consistent query writing practices when leveraging these SQL Server features for performance optimization.
CHAPTERS
00:00:00 - Introduction
00:02:04 - Filtered Indexes and Sargability Issues
00:05:16 - Computed Columns and Query Performance
00:08:39 - Practical Examples with Filtered Indexes
00:11:13 - Best Practices for Filtered Indexes and Computed Columns
00:12:12 - Conclusion
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 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
━━━━━━━━━━━━━━━━━━━━━━━━━━