SQL Server Performance

declare table vs create table # in sp

Discussion in 'Performance Tuning for DBAs' started by pyao88, May 26, 2005.

  1. pyao88 New Member

    What are the differences between these two? They seem to work the same.

    Is declare table also using tempdb database? Or does it use pure memory?
    I noticed that I don't need to drop the declared table.

    Is declare table always better than create table #xyz syntax?

    declare @accounts table (
    account_id numeric(10,0) not null primary key)

    create table #accounts (
    account_id numeric(10,0) not null primary key)
  2. Madhivanan Moderator

  3. rlahoty New Member

    Sometimes the performance impact can be huge. I have seen table variables not generating parallel query plans on a large SMP box while in the same circumstances, temporary tables did create them.

    Also SQL Server maintains statistics for queries that use temporary tables but not for queries that use table variables. In general, you should use table variables for smaller data set queries.

    HTH
    -Rajeev Lahoty
  4. gurucb New Member

    Table Variables:
    Advantages:
    in memory so access is faster.
    No recompilation of stored procedures (Thus reducing CPU cost)
    Disadvantages:
    If the table size is big then sQl Server automatically writes to Tempdb.
    There can be no indexes and statistics.

    So if data returned is more, then create temptable

    and if data returned in is very less then create table variable.

Share This Page