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: these days I have AI do the annoying stuff for me. My new video course teaches you to work the same way, and I teach whole teams in a one-day workshop. Someone still has to know enough SQL Server to keep the robots in check, and that’s what my training is for.