How to insert output of exec into a table variable (not a temp table)

[calvinsinger]

Hi,

I wanted to be able to use table variables with an exec, or be able to insert output of dynamic sql into a table variable instead of a temporary variable.

I am not sure if there is much of performance gain, since table variables are in memory, but if there is, I would like to take advantage of it.

Thank you
Calvin

 

[nigelrivett]

There would be a performance gain but unfortunately it is not possible.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[JayKusch]

This article came from SQL Server Magazine, Nov 2003 ...

Table Variables vs. Temporary Tables

In general, is there a performance difference between using table variables and using temporary tables in SQL Server 2000?

There can be a big performance difference between using table variables and temporary tables. In most cases, temporary tables are faster than table variables. I took the following tip from the private SQL Server MVP newsgroup and received permission from Microsoft to share it with you. One MVP noticed that although queries using table variables didn't generate parallel query plans on a large SMP box, similar queries using temporary tables (local or global) and running under the same circumstances did generate parallel plans.

A senior member of the SQL Server development team told me that table variables use internal metadata in a way that prevents the engine from using a table variable within a parallel query. He also said that SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. Without statistics, SQL Server might choose a poor processing plan for a query that contains a table variable. The development team member added that you should limit your use of SQL Server 2000 table variables to reasonably small queries and data sets and use temporary tables for larger data sets.

This advice about table variables contradicts some past Microsoft information, which said that table variables were faster because they're created in memory. However, table variables can incur disk I/O in ways similar to temporary tables.

In deciding whether you should use table variables or temporary tables, benchmark both in your environment. I suspect that temporary tables will provide better performance on larger result sets in most cases. For more information about table variables, see the Microsoft article "INF: Frequently Asked Questions—SQL Server 2000—Table Variables" at

http://support.microsoft.com/default.aspx?scid=kb;en-us;305977.

Thanks

J. Kusch

 

[nigelrivett]

To clarify a bit.
Table variables can involve disk i/o but the major gain is that they do not allocate space in tempdb in the same way so get round the contention problem that occurs in a multi-user system with temp tables.
If you have a system which creates a lot of small tables on different connections (e.g. a web site) then you should see performance gains by using table variables.
If you create few but large temp tables then you could see a performance loss through using table variables.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.