Table Variables

These little buggers have so many issues, it’s hard to know where to begin. One common misconception is that they reside purely in memory. That’s wrong; they’re all backed by temporary objects, and may very well spill to disk when you run of of scratch space.

Other lesser known problems:

Very poor cardinality estimates (no statistics generated)
Modifications (inserts, updates, deletes) are serialized

You can get around the poor cardinality estimates by using OPTION(RECOMPILE), but then you’re shooting the one upside to table variables squarely in the foot: they don’t cause a recompile where temp tables might in a stored procedure.

They’re totally fine for maintenance-type tasks where performance isn’t a concern, or if you’re really sure that you’re never going to have more than a few rows sitting in them.

Other than that, you should test out replacing them with temp tables. The indexing is much more flexible, and SQL will generate statistics to aid cardinality estimation.