Wind Charms
After yesterday’s post about when to use or not use @table variables in SQL Server, you can probably make choices more confidently.
Most of the time, you want to be using #temp tables, when plan choices and statistics matter to overall performance, and @table variables when code executes at a high frequency over a small-ish number of rows, where plan choices and statistics don’t matter to overall performance.
In case you didn’t pick that up, or something.
Let’s move on.
Use Cases For #Temp Tables
The best use cases for #temp tables are for materializing things like:
- Non-SARGable expressions
- Complicated CTEs or Derived Tables
- Parameter-sensitive portions of queries
- CTEs that need to be referenced multiple times
Just to name a few-plus-one things that can generally be improved.
There are many more, of course. But getting overly-encyclopedic in blog posts tends to be over-productive. Plus, no one reads them, anyway.
What I think the real value of breaking queries up into more atomic pieces is, though, is that it’s a bit easier to isolate exactly which parts are the slowest, and work on them independently.
When you’ve got one gigantic query, it can be difficult to tune or figure out how all the different pieces interact. What’s slow for one execution might be fast for another, and vice-versa.
Chomper
Of course, temporary objects aren’t always strictly necessary. Sometimes it’s enough to break disjunctive predicates up into UNION-ed clauses. Sometimes having the right index or using batch mode can get you where you need to go.
Choosing to use a temporary object comes with choices:
- Can I afford to take up this much space in tempdb?
- Can I afford to execute this under high concurrency?
- Have I exhausted other options for tuning this query?
You don’t necessarily need to answer all of those things immediately, but you should exercise some domain knowledge during tuning efforts.
Thanks for reading!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.