Rewriting Multi Statement Table Valued Functions
In this video, I dive into the world of multi-statement table-valued functions (TVFs) and their notorious performance issues. I explain why these functions are generally a bad idea, even for simple queries, due to the overhead of table variables. However, with SQL Server 2017's interleaved execution feature, there is hope! I walk you through how to rewrite these complex TVFs into inline TVFs using common table expressions (CTEs) and startup expression predicates. By doing so, we can significantly improve performance by reducing the number of times tables are accessed, leading to more efficient execution plans. Whether you're a seasoned SQL pro or just starting out, this video offers practical tips on how to tackle these tricky functions and optimize your queries for better performance.
CHAPTERS
00:00:00 - Introduction
00:01:31 - Rewriting Multi-Statement Functions
00:02:24 - Why Direct Rewrite Fails
00:03:03 - Using CTEs for Conditional Logic
00:04:05 - Execution Plan Analysis
00:05:37 - 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
━━━━━━━━━━━━━━━━━━━━━━━━━━