Post reply

Comparing Table Variables with Temporary Tables

  • June 8, 2012 at 3:59 am

    #1498604

    Thanks to Wayne for an awesome article! And thanks others for great discussion.

    There's one point that no one picked up that I find inaccurate:

    Note that the estimated execution plan for both table variables and temporary tables will show 1 row;

    Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table??

    Execute:

    select 1 id into #obs

    union select 2

    union select 3

    union select 4

    union select 5

    go

    and then examine the estimated execution plan for:

    select * from #obs;

    go

    The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?

  • June 8, 2012 at 5:38 am

    #1498659

    Very well done. I especially like the comparison table you produced. I have plans to refer some developers to read this article as the misuse of Table Variables is rampant in the current shop.

    In the article:

    Microsoft recommends using table variables (in Reference 4).

    What would be unfortunate is if the initial adoption of such practices were due to a poorly worded recommendation from Microsoft so I would like to read their actual recommendation. I visited Reference 4 as shown as T-SQL BOL (SQL 2008), CREATE TABLE however I must be missing it. I would like to have my bases covered before I bring the issue for discussion with the group. Can you point me to the recommendation made by Microsoft, or restate it here?

    Thanks again for a great read.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • June 8, 2012 at 10:28 am

    #1498828

    I wish I could rate it more than 5 stars. Excellent article. Concise and precise.

  • June 8, 2012 at 9:14 pm

    #1498998

    Really good article.

    I will read it more in details.

    Thanks Wayne!

  • June 8, 2012 at 9:40 pm

    #1498999

    Very good article, thanks.

    And apparently I wasn't the only one who must have missed it the first time around, so it is also a good illustration of the value of resurfacing older articles.

  • June 9, 2012 at 8:06 am

    #1499014

    I know it's a reprint but I have to say it again... I consider this to be the definitive article on the differences between Table Variables and Temp Tables. It has opened the eyes of several people that I know, especially about the myth that Table Variables are "memory only" and that Temp Tables are "disk only".

    Very well done, Wayne.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 10, 2012 at 1:46 pm

    #1499114

    Superb article. Well worth the read.

  • June 11, 2012 at 11:31 am

    #1499408

    opc.three (6/8/2012)

    Very well done. I especially like the comparison table you produced. I have plans to refer some developers to read this article as the misuse of Table Variables is rampant in the current shop.

    In the article:

    Microsoft recommends using table variables (in Reference 4).

    What would be unfortunate is if the initial adoption of such practices were due to a poorly worded recommendation from Microsoft so I would like to read their actual recommendation. I visited Reference 4 as shown as T-SQL BOL (SQL 2008), CREATE TABLE however I must be missing it. I would like to have my bases covered before I bring the issue for discussion with the group. Can you point me to the recommendation made by Microsoft, or restate it here?

    Thanks again for a great read.

    The way that MS does their SQL BOL links, that link now points to the SQL 2012 BOL entry. Check out the SQL 2008 (not R2) link at: http://msdn.microsoft.com/en-us/library/ms174979%28v=sql.100%29.aspx - it's in the "Temporary Tables" section, and it has been removed from the SQL 2008R2 / 2012 sections.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 11, 2012 at 11:57 am

    #1499415

    sparky-407434 (6/8/2012)

    I had been using temp tables in a large import process and made the change to table variables and saw a big improvement in performance. However, the server was later moved to a virtual server (VMWare) and the processes ran much slower (x40 slower). I reverted back to temp tables and the process improved again (though not to the original speed on physical server using table variables)

    I think we need more discussion when looking at how different methods interact with disk / memory, as to how they compare across both physical and virtual environments. In my case what is good for physical was bad for virtual and vice versa. Or perhaps there are things which the VM administrators need to do to overcome this problem.

    And this highlights one very important thing... always test in your environment. Your results may be different, and may change over time. Thanks for bringing this up Sparky!

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 11, 2012 at 12:00 pm

    #1499416

    Andrew Diniz (6/8/2012)

    Thanks to Wayne for an awesome article! And thanks others for great discussion.

    There's one point that no one picked up that I find inaccurate:

    Note that the estimated execution plan for both table variables and temporary tables will show 1 row;

    Agreed, the cardinality for the table variable is 1 row (without recompilation) but the temporary table??

    Execute:

    select 1 id into #obs

    union select 2

    union select 3

    union select 4

    union select 5

    go

    and then examine the estimated execution plan for:

    select * from #obs;

    go

    The full table scan of #obs has cardinality 5, not 1, even before the query is executed and the plan cached. Given that statistics are created for temporary tables this is exactly what I'd expect so am confused why you would suggest otherwise. What have I missed?

    Andrew, I do believe that you are right... you found something that was missed the first time around. I'll have to look into this tonight and respond.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 11, 2012 at 12:06 pm

    #1499419

    I want to thank Lawrence, Andrew, opc, Skamath, Haining, Alex, Jeff and Sean for all the kind and positive words for me. Thanks a bunch for the feedback.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • June 18, 2012 at 5:05 pm

    #1502509

    Most comprehensive comparison table variables and temp tables I've ever seen. Thanks

  • June 18, 2012 at 5:58 pm

    #1502526

    Thank you Patrick... It is due to responses like yours that makes me want to do this.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • September 7, 2013 at 2:16 am

    #1648294

    Its a great article, thanks for sharing it.

    Neeraj Prasad Sharma
    Sql Server Tutorials

Viewing 14 posts - 151 through 163 (of 163 total)

You must be logged in to reply to this topic. Login to reply