Past Perfect
You know all those pesky developer myths that never go away?
- CTEs and Views can’t use indexes: WRONG
- Subqueries are slower than joins: WRONG
- Temp tables are bad for performance: WRONG
- Table Variables are always in memory: WRONG
Okay, that’s not a complete list, but a complete list would fill the internet to its very brim and cause all sorts of overflow errors.
My job as a performance tuning consultant is to teach folks when they’ve been lead astray.
To confuse things a bit further, Microsoft allows for in-memory @table variables, but you have do a lot of work to get set up to use them.
They’re not the ones you get out of the box.
Future Mediocre
Let’s say for the sake of argument that @table variables were always more “in-memory” than #temp tables (they’re not). They’d still have a lot of the same fundamental problems:
- Modifications run single-threaded
- You don’t get accurate table cardinality (2019+, with a lot of caveats)
- There’s no column-level statistical information
With all that stuff in mind: who cares if they’re only in memory? There are so many downsides for most query patterns, they could all happen in quantum space and performance would still stink.
I know, they have their uses:
But most of y’all aren’t using them that wisely.
And here’s the other thing: Most of y’all don’t have enough memory to begin with, and the memory you do have you misuse in a million ways. Why do you wanna make that worse?
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 database performance problems quickly. You can also get a quick, low cost health check with no phone time required.