SQL Server Performance

Join between two @table variables

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, May 31, 2009.

  1. atulgoswami New Member

    What is the difference between in below two scenarios (from performance point of view)?
    (1) Join between two @table variables
    And
    (2) Join between @table variable and #temp table
    Thanks
    Aero
  2. ndinakar Member

    there's no difference.. you can join any of these types in a single query.
  3. Madhivanan Moderator

    [quote user="atulgoswami"]
    What is the difference between in below two scenarios (from performance point of view)?
    (1) Join between two @table variables
    And
    (2) Join between @table variable and #temp table
    Thanks
    Aero
    [/quote]
    Performance problem occurs if there are no indices and too much data on the tables
  4. Adriaan New Member

    Note that with a table variable, the only index you can have is by setting a primary key. If you need (an)other index(es) you can only use temp tables.
  5. FrankKalis Moderator

    Additionally table variables are limited in the type of constraints available to them. And also no statistics are kept for them.
  6. atulgoswami New Member

    [quote user="FrankKalis"]
    Additionally table variables are limited in the type of constraints available to them. And also no statistics are kept for them.
    [/quote]
    My question was on different lines. What is the advantage if join one #temp table and @table variable OVER between two @table variable.
    I was in discussion and there somebody said that Joining one #temp table and @table variable has performance advantage OVER join between two @table variable. Howevere i could not think of any reason behind this.
    Thanks
  7. FrankKalis Moderator

    [quote user="atulgoswami"]
    [quote user="FrankKalis"]
    Additionally table variables are limited in the type of constraints available to them. And also no statistics are kept for them.
    [/quote]
    My question was on different lines. What is the advantage if join one #temp table and @table variable OVER between two @table variable.
    I was in discussion and there somebody said that Joining one #temp table and @table variable has performance advantage OVER join between two @table variable. Howevere i could not think of any reason behind this.
    [/quote]
    There is no advantage of one method over the other. A JOIN operation is a JOIN operation. However, temporary tables and table variables serve different purposes and should be used differently. Here is a good starting point: http://blogs.msdn.com/sqlserverstor...-table-variable-vs-local-temporary-table.aspx
  8. FrankKalis Moderator

    Small addition: However, as I mentioned before because of the limited possibility to define indices on table variables it might hurt performance when you JOIN to a table variable on non-key column(s).

Share This Page