Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

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.

share|improve this answer
add comment

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

share|improve this answer
3  
Add to that the fact that temp tables can have indexes, have statistics, while table variables can only have a PK. You have some details here. –  Marian Jul 25 '11 at 20:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.