In Memory Table Variables In SQL Server: Still Pretty Stupid
In this video, I dive into the world of in-memory table variables in SQL Server, specifically focusing on their behavior under different compatibility levels. Starting off with a lighthearted introduction, I create a database named "Trash" and populate it with data from Stack Overflow's 2013 post table to demonstrate how cardinality estimation works for these memory-optimized table variables. I then delve into the query plans generated by SQL Server under compatibility level 140, showing how the system consistently estimates only one row, despite having full knowledge of the actual number of rows in the variable. Moving on to compatibility level 150 (assuming you've paid Microsoft's "Can't Buy Me Love" tax), I reveal that while parallel execution plans are still not supported, SQL Server does provide more accurate cardinality estimation for table variables without a WHERE clause, estimating the full 27,901 rows. The video concludes with an exploration of how these table variables can lead to unexpected behavior and potential performance issues, making me question their practical utility in real-world scenarios.
CHAPTERS
00:00:00 - Introduction
00:02:21 - In-Memory Table Variables
00:04:01 - Expert Levels
00:05:25 - Upcoming Events
00:06:23 - AI Image Making
00:07:12 - Future of AI
00:08:02 - In-Memory Table Variables
00:09:08 - Feature Critique
00:10:59 - Table Type Creation
00:14:05 - Query Limitations
00:17:13 - Reusing Queries
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 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
━━━━━━━━━━━━━━━━━━━━━━━━━━