DECLARE table variable explicitly or by using type?

• bdcoder

  SSC Eights!

  Points: 909

  More actions

  December 1, 2015 at 6:10 pm

  #305719

  Hello,

  Just wondering if anyone has any performance measures with regard to declaring table variables.

  Method 1 - Declare table variable with required columns, ie:

  DECLARE @ap_tbl TABLE ( col1 UNIQUEIDENTIFIER, col2 INTEGER, col3 DATETIME );

  Method 2 - Create a user defined table type and then use that type to declare the table variable, ie:

  /* Create user defined table type; done once ... */

  CREATE TYPE [dbo].[udt_ap_tbl] AS TABLE(

  [col1] [uniqueidentifier],

  [col2] [int],

  [col3] [datetime] );

  And then, when you need to declare the table variable ...

  /* Create table variable using type ... */

  DECLARE @ap_tbl AS dbo.udt_ap_tbl;

  I am assuming method 2 would be quicker as the table structure is already defined, but I have not found any articles that prove this.

  Thanks in advance.

• SoHelpMeCodd

  SSCertifiable

  Points: 5871

  More actions

  December 1, 2015 at 8:20 pm

  #1843385

  Likely to be bound by SGAM/GAM page contention in tempdb, but could be CPU bound. Should be easy to benchmark on your CPUs, with your tempdb files (mine differ). Duration can/should be measured in microseconds, but with millisecond accuracy. Best to iterate at least a thousand. Also test up to a hundred concurrent sessions.

  But the above benchmark worries me less than a proposed use of a table variable, when the topic is performance. I am hoping you are well-versed in performance, execution plans, cardinality estimates, and table variables ;-).

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  December 2, 2015 at 2:04 am

  #1843424

  You'll probably find the time difference to be utterly meaningless and under the error range for measuring duration of statements. Worrying about a couple microseconds here or there is likely to be a waste of time.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass