Table Variable cartoon

Quiz: Temporary Tables vs. Table Variables

Table Variable cartoon
I declare, it’s a table variable

Quiz: Temp Tables vs Table Variables 🤜🤛

Who doesn't love tempdb?
  • Pick the one true item in the list.
  • I promise, one of the answers is true, even if they all look false.
  • I don't expect you to remember the trace flag number!
    There is ONE major trace flag introduced to help with table variable performance in recent history, and this is that flag. No trickery there.
  • By "matter", I mean "make a difference to performance." I don't mean philosophically. That's a personal choice.

Related links and good reading

Gail Shaw busts some myths about table variables

Including facts about indexing and logging for table variables.

Paul White teaches you about how temp tables and their statistics can be cached

This is a long read, but very worthwhile.

Paul White gives you even more tempdb goodness

Including things like the impact of dropping temp tables in procedures.

The deets on Trace flag 2453 by Aaron Bertrand

Note that the article starts with an example that shows that the trace flag doesn’t work in every single situation (it doesn’t work for his trivial plan – but that’s an exception, not the rule).