SQL Server Performance

#table vs @table in SQL 2000

Discussion in 'Performance Tuning for DBAs' started by WingSzeto, Nov 1, 2006.

  1. WingSzeto Member

    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
  2. Adriaan New Member

    #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.
  3. joechang New Member

    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
  4. ranjitjain New Member

  5. Madhivanan Moderator

  6. Roji. P. Thomas New Member

    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
  7. Adriaan New Member

    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."

Share This Page