SET NOCOUNT ON

• David.Poole

  SSC Guru

  Points: 76191

  More actions

  April 2, 2004 at 4:07 am

  #501566

  I have been doing some experimenting with routines to traverse down a tree structure with temporary tables vs table variables.

  God knows how but the rowcount thing resolved itself

  In terms of performance I have found that table variables

  • Offer consistent performance
  • Don't seem to gain from primary keys until you join them to a physical table/view object.
  • Have a performance penalty in that you cannot use TRUNCATE with a table variable.

  Conversely temporary tables

  • Give varying performance.
  • Are VERY sensitive to the use of primary keys.
  • Offer significant benefits if you need to TRUNCATE records.

  For some reason an insert into a temporary table seems to be faster than an insert into a table variable except if you do

  INSERT @tbl1 SELECT * FROM @tbl2

  In which case the opposite seems to be true.

  LinkedIn Profile