Table variables cannot replace temp tables in all scenarios.If that was the
case, there would have been no support for the latter in SQL2000.What to use
and when depends on the need.We have seen table variables doing good when
theres lot of DML actions.May be because of less locks and recompiles.For
inserting a number of rows initially , temp tables have proved good.I guess,
the delay for table variables is in the switchover between memory and the
spool table.So the bottomline is, test it against your code and make the
switch where it proves wise.Heres a good faq on table variables:
INF: Frequently Asked Questions - SQL Server 2000 - Table Variables
SQL Server FAQ at
"Dave Slinn" <dslinnaccesscomm.ca> wrote in message
news:uDAO5hMRDHA.2852tk2msftngp13.phx.gbl...on> I have read a statement that recommends to use the new 'table' data type
> instead of temporary tables, as it can improve performance.
> What are your experiences with this? The doentation says it cannot be
> used for two cases, INSERT INTO's and SELECT INTO's. Any good resources> the web? The SQL doentation doesn't seem to be very thorough.
> - Dave