Table Variables

By Steve Jones, 2010/06/02

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:

create table TableVarMatch(
  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:

declare @MyTableCar Table
  ( 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:

 declare @MyTableCar 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.