I was never a big fan of temp tables, mostly because there were serious contention issues in SQL Server v6.5. I know things were better in v7.0, but I managed to learn to code without them, so I have avoided them for years.
When table variables came, I was in the habit of not using temp space, so I didn’t use them much, but I have seen them used more and more and decided to experiment a little with the recently.
I looked at the Table Variables article on SQLServerCentral as well as this article on Table Variables and a KB article. Without digging into pros/cons, etc. here’s a little code:
I can create a table like this:
This gives me two rows back, as you would expect. I can do the same thing with a table variable:
And just like a table, I can actually insert data from another table:
This can be handy for small data sets. I think I’d use this if I quickly needed to store some data in a series of steps instead of a temp table, and if I couldn’t materialize a CTE, this would work well.
You can’t index these, and just like temp tables, these will get loaded into tempdb if needed, but it’s a handy construct that can be returned from a function (TVF). Definitely something that I need to play with a little.