@table_variable or #temp_table

Question

I have a big user defined table type variable that has 129 columns. I will store around 2000-3000 records in this table variable at a time and pass it to various stored procedures and functions to get additional data and make modifications. These additional data and new modifications will then be stored in a new table variable of the same type and returned to the source stored procedure via an OUTPUT parameter. (This is because a table type parameter can only be passed as READONLY.)

This is my pseudo-code:

  SP1
  @tmp tableType
  {
        INSERT @tmp EXEC
        SP2 (@tmp)

        INSERT @tmp EXEC
        SP3 (@tmp)
  }

Should I use a @table_variable or #temp_table?

Answer 1

There are two blogs you should look at. The first (here) is a comparison of Table Variables and Temp Tables. It is from 2008 and remains relevant for SQL Server 2008 R2.

The second blog entry (here) deals with some of the misconceptions (missed-conceptions) regarding Table Variables; including indexing a Table Variable.

Both of these blog entries, articles, are written by Gail Shaw.

I believe the one telling item you are looking for is a Table Variable may not be passed between stored procedures while a Temp Table is able to be passed between stored procedures. The closest a Table Variable comes to passing between stored procedures is as a User-Defined Table Type.

Passing a Table Variable as a TVP to a stored procedure has the requirement it must be defined as a READ-ONLY parameter. This implies there will be multiple copies of the Table Variable if you want to return a modified copy of it.

All told, passing between stored procedure may be better served by a Temp Table when you want to return the passed-in structure with updated values.

Answer 2

Nothing better than trying both, but I find #tempTables perform better once you get into the hundreds of records.