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:
Myid int
, myChar varchar(20)
)
go
insert TableVarMatch select 1, 'A'
insert TableVarMatch select 2, 'B'
go
select * from TableVarMatch
This gives me two rows back, as you would expect. I can do the same thing with a table variable:
( MyID int
, MyChar varchar(20)
)
insert TableVarMatch select 1, 'A'
insert TableVarMatch select 2, 'B'
select * from @MyTableCar
And just like a table, I can actually insert data from another table:
( MyID int
, MyChar varchar(20)
)
insert @MyTableCar select * from TableVarMatch
select * from @MyTableCar
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.
