You know all those pesky developer myths that never go away?
Category: Table Variables
Software Vendor Mistakes With SQL Server: Misusing @Table Variables
Temporary objects are one of those tricky things. You probably know you should be using them for certain things, but which one to use is a constant source…
Read the post: Software Vendor Mistakes With SQL Server: Misusing @Table Variables
Using Batch Mode for the “Matching Supply With Demand” Challenge
Itzik Ben-Gan posted an interesting T-SQL challenge on SQL performance dot com. I’m writing up my solution in my own blog post because I have a lot to say…
Read the post: Using Batch Mode for the “Matching Supply With Demand” Challenge
How #Temporary Tables Can Cause Plan Cache Pollution In SQL Server
I solve a lot of problems with #temp tables, indeed I do. And I hate people who are reflexively anti-#temp table.
Read the post: How #Temporary Tables Can Cause Plan Cache Pollution In SQL Server
LOB Data, Recompile, And Too Much tempdb Usage In SQL Server
I got a very interesting issue about sp_HumanEvents recently, and I couldn’t quite figure out why things were going wonky.
Read the post: LOB Data, Recompile, And Too Much tempdb Usage In SQL Server
When Query Spills Cause tempdb Contention And Performance Issues In SQL Server
I was helping a client troubleshoot a scalability issue recently, where during one point in their workload tempdb went out of control.
Read the post: When Query Spills Cause tempdb Contention And Performance Issues In SQL Server
SARGability Week: Using Temp Tables To Fix Non-SARGable Query Performance Problems In SQL Server
This post is especially interesting because it highlights the oddball performance situations you can run into when you write the type of All-In-One™…
Local Variables Get Bad Cardinality Estimates In Cursors In SQL Server, Too
While helping a client out with weird performance issues, we isolated part of the code that was producing a whole bunch of bad plans.
Read the post: Local Variables Get Bad Cardinality Estimates In Cursors In SQL Server, Too
Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries
I see people writing stored procedures that use local variables either because they read that all functions in a where clause are dangerous, or because…
Read the post: Why Expressions Are Better Than Local Variables For Performance In SQL Server Queries
Things SQL Server vNext Should Address: Table Variable Modification Performance
People still tell me things like “I only put 100 rows in table variables”, and think that’s the only consideration for their use.
Read the post: Things SQL Server vNext Should Address: Table Variable Modification Performance
Things SQL Server vNext Should Address: Common Table Expression Materialization
I know what you’re thinking: this is another post that asks for a hint to materialize CTEs.
Read the post: Things SQL Server vNext Should Address: Common Table Expression Materialization
Things SQL Server vNext Should Address: Local Variable Estimates
I’ve written at length about what local variables do to queries, so I’m not going to go into it again here.
Read the post: Things SQL Server vNext Should Address: Local Variable Estimates
When Should You Use Table Variables In SQL Server? When Queries Execute Thousands Of Times A Minute
Table variables beat temp tables when a workload runs tiny queries at very high frequency and very high concurrency, because temp tables hit tempdb…
SQL Server Spool Operators Are Just Crappy Temp Tables
Using the scenario from yesterday’s post as an example of why you might want to think about rewriting queries with Table Spools in them to use temp tables…
Read the post: SQL Server Spool Operators Are Just Crappy Temp Tables
What’s Really Different About In-Memory Table Variables In SQL Server?
My dear friend Kendra asked… Okay, look, I might have dreamed this. But I maybe dreamed that she asked what people’s Cost Threshold For Blogging™ is.
Read the post: What’s Really Different About In-Memory Table Variables In SQL Server?
Tracking Row Changes With Temporal Columns In SQL Server
Temporal tables are cool, but keeping all that history can be stressful. Change Tracking adds overhead to every transaction, and requires Snapshot…
Read the post: Tracking Row Changes With Temporal Columns In SQL Server
Don’t Be Afraid Of tempdb In SQL Server
When tuning queries, one pattern I see over and over again is people running crazy-long queries.
Making The Most Of Temp Tables Part 4: Batch Mode
When you have queries that need to process a lot of data, and probably do some aggregations over that lot-of-data, batch mode is usually the thing you…
Read the post: Making The Most Of Temp Tables Part 4: Batch Mode
Making The Most Of Temp Tables Part 3: More Opportune Indexes To Make Queries Go Faster
You have too many indexes on too many tables already, and the thought of adding more fills you with a dread that has a first, middle, last, and even a…
Making The Most Of SQL Server Temporary Tables Part 2: Materializing Expressions
A lot of the time when I see queries that are written with all sorts of gymnastics in the join or where clause and I ask some questions about it, people…
Read the post: Making The Most Of SQL Server Temporary Tables Part 2: Materializing Expressions
Making The Most Of Temp Tables In SQL Server Part 1: Fully Parallel Inserts
If you have a workload that uses #temp tables to stage intermediate results, and you probably do because you’re smart, it might be worth taking advantage…
Read the post: Making The Most Of Temp Tables In SQL Server Part 1: Fully Parallel Inserts
Starting SQL: I Don’t Care How Many Rows Are In Your Table Variable, They Can Still Hurt Performance
So you’re that odd soul who has been listening to rumors about table variables. Perhaps things about them only being in memory, or that they’re okay to use…
Starting SQL: Why Your Query Can’t Go Parallel, Table Variables Edition
The important thing to understand about parallelism is it’s great when appropriate. Striking a balance between what should go parallel, the CPU usage it’s…
Read the post: Starting SQL: Why Your Query Can’t Go Parallel, Table Variables Edition
Experiment With Table Variable Deferred Compilation Without SQL Server 2019
If you haven’t heard by now, Table Variables have some ✌features✌ that can cause performance issues pretty generally in SQL Server.
Read the post: Experiment With Table Variable Deferred Compilation Without SQL Server 2019
The SQL Server Performance Tasting Menu: How Table Variables Cause And Hide Performance Problems
Table variables can beat temp tables when code runs at very high frequency on few rows and plan shape does not matter, but column-level statistics make…


