#table vs @table in SQL 2000

We are using SQL 2K with SP4.

Can anyone explain to me if using @table is better than using #table from the performance standpoint? Also, I believe both tables are stored in RAM and will SQL 2K process #table same way as @table in the RAM? Is there any limitations or benefits on each one?

wingman

#table is always created 'on disk'.

@table is created in RAM if the expected size does not exceed a given threshold. If it is expected to exceed the threshold, then the @table is created 'on disk' anyway.

There are some things you can do with #table that you can't do with @table, but usually @table is a safe bet.

i am not convinced that there is a huge difference
i would not use @t for a very large table, due to virtual address space concerns
@t does not trigger the 6 row recompile the way #t does, but this can be inhibited

Check this article:
http://www.developer.com/db/article.php/3414331

quote:Originally posted by Adriaan

#table is always created 'on disk'.

@table is created in RAM if the expected size does not exceed a given threshold. If it is expected to exceed the threshold, then the @table is created 'on disk' anyway.

There are some things you can do with #table that you can't do with @table, but usually @table is a safe bet.

If memory is available, both table variable and temprary table is created and processed in memory. See KB 305977

From a performance perspective, my recommendation is, use table variable for small sets and temorary tables for relatively large sets.

Also see
Difference between Table Variable and Temporary Table



Roji. P. Thomas
http://toponewithties.blogspot.com

Roji,

Thanks for the KB reference - my version was what I learned from various discussions on this forum. As usual - "All postings are provided “AS IS” with no warranties for accuracy."