Comparing Table Variables with Temporary Tables

  • Andrew Diniz

    SSCommitted

    Points: 1852

    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?

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    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

  • skamath

    Valued Member

    Points: 64

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

  • Haining

    SSCrazy

    Points: 2118

    Really good article.

    I will read it more in details.

    Thanks Wayne!

  • Alex Fekken

    Ten Centuries

    Points: 1109

    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.

  • Jeff Moden

    SSC Guru

    Points: 996661

    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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

  • Sean Smith

    SSCertifiable

    Points: 5903

    Superb article. Well worth the read.

  • WayneS

    SSC Guru

    Points: 95384

    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

  • WayneS

    SSC Guru

    Points: 95384

    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

  • WayneS

    SSC Guru

    Points: 95384

    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

  • WayneS

    SSC Guru

    Points: 95384

    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

  • Patrick Ge

    SSC Eights!

    Points: 912

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

  • WayneS

    SSC Guru

    Points: 95384

    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

  • Neeraj Prasad Sharma

    Ten Centuries

    Points: 1285

    Its a great article, thanks for sharing it.

    Neeraj Prasad Sharma
    Sql Server Tutorials

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

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