A Lot About Multi-Statement Table Valued Function In SQL Server

Description:
In this video, I dive into the intricacies of multi-statement table-valued functions in SQL Server, specifically focusing on their behavior and performance implications. I start by introducing a function that returns a table variable based on optional parameters, highlighting why such constructs are generally discouraged unless you're a seasoned T-SQL practitioner. The discussion then delves into the concept of interleaved execution, explaining how this feature can lead to unexpected performance issues when the function is executed multiple times with different inputs. I walk through several examples and query plans, illustrating how these functions behave differently based on whether literal values or columns are passed as parameters, and how recompiling the function can mitigate some of these issues while introducing others. By the end of the video, you'll have a better understanding of when to use multi-statement table-valued functions and how to optimize their performance in various scenarios.

CHAPTERS

00:00:00 - Introduction
00:00:59 - Data Community Summit in Seattle
00:02:23 - Multi-statement Table Valued Functions
00:03:17 - Skilled T-SQL Practitioner Required
00:04:07 - Query Execution Result
00:04:51 - Number of Rows Returned
00:05:34 - Interleaved Execution Explained
00:06:22 - Optimizer Feature Released in 2017
00:08:03 - Function Execution Time Issue
00:09:07 - Using Creation Date and Last Access Date
00:10:58 - Correlation Inside the Function

━━━━━━━━━━━━━━━━━━━━━━━━━━
???? TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-ai-ready-with-erik?coupon=AIREADY

SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-server-performance-engineering?coupon=ENGINEERING

Learn T-SQL with Erik
https://training.erikdarling.com/learn-t-sql-with-erik?coupon=ADVANCEDTSQL

Everything Bundle:
https://training.erikdarling.com/?coupon=SPRINGCLEANING

━━━━━━━━━━━━━━━━━━━━━━━━━━
????️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlconsulting

━━━━━━━━━━━━━━━━━━━━━━━━━━
???? CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/

Become a channel member
https://www.youtube.com/@ErikDarlingData/join
━━━━━━━━━━━━━━━━━━━━━━━━━━