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.
Read more here:
Are Table Variables as Good as Temporary Tables in SQL 2014?
Temp Tables vs Table Variables vs Memory Optimized Table Variables [Video]