Comparing Table Variables with Temporary Tables

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719130

    I've put it back in the article text, so you can read it online with colored formatting.

    My apologies to Wayne for not getting this done sooner. I thought I had saved it, but apparently I didn't.

  • WayneS

    SSC Guru

    Points: 95373

    Dean Cochrane (6/10/2009)


    Damn good article. I knew that table variables didn't have stats computed for them, but I hadn't fully considered the implications of that.

    Thank you.

    One point which your article doesn't make explicitly is that table variables live outside transactions. I realize that you show this in the comparison table at the bottom, but I think an article as good as this one should give this point at least a mention in the Other Differences section, because this behavior is 1. potentially useful and 2. an (admittedly) rare source of difficult-to-find bugs.

    Excellent point. While the article doesn't explicitly state that table variables live outside of transactions, at the beginning of the article, under the "Table Variables" section, points 3) and 4) both talk about how table variables interact with transactions.

    Edit: clarification on transactions within the article.

    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

  • Adam Haines

    SSC-Insane

    Points: 23197

    Great job Wayne! This article is definitely a good read. 🙂

  • WayneS

    SSC Guru

    Points: 95373

    Steve Jones - Editor (6/10/2009)


    I've put it back in the article text, so you can read it online with colored formatting.

    My apologies to Wayne for not getting this done sooner. I thought I had saved it, but apparently I didn't.

    Thanks Steve. I personally think that the online article looks better now.

    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

  • Andy Lennon

    SSCrazy

    Points: 2354

    This is the perfect resource for Temp Table/Table Variable comparisons as far as i'm concerned. The graph is a particularly useful quick reference.

    Well done, and many thanks.

  • SQLEnthusiastic

    Mr or Mrs. 500

    Points: 593

    Dropping of global temp table can be tested this way too:

    Create the global temp table in a query window.

    Close it.

    Open another query window and try to access it.

    Excellent article !

  • WayneS

    SSC Guru

    Points: 95373

    Andy Lennon (6/10/2009)


    This is the perfect resource for Temp Table/Table Variable comparisons as far as i'm concerned. The graph is a particularly useful quick reference.

    Thank you...

    Well done, and many thanks.

    ... and you're welcome.

    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

  • Alberto dbLearner

    Ten Centuries

    Points: 1246

    Wow, I just hit the ground hard!!

    Excelent!!

    Alberto

    Alberto De Rossi
    Microsoft Certified Solutions Associate - SQL Server
    Microsoft MVP - Data Platform
    Power BI User Group Lima - Community Leader

  • Dave Coats

    SSC Eights!

    Points: 885

    Excellent article Wayne, well written and very informative. Interesting how the system names are assigned to the temptables. Also, helpful that you CAN add indexes to table variables (Myth #3). I found the quick reference chart at the end particularly useful as well. Looking forward to the next article. 🙂

    Dave Coats

  • shaili

    Right there with Babe

    Points: 753

    Excellent article. Cleared up all the questions/myths aboout temp tables /variables.

    Thank you..

  • Tim Walker.

    SSCertifiable

    Points: 5173

    Excellent article. Very comprehensive about the theory!

    Question: (and I wouldn't have even asked it if I hadn't read this first) is what is the point in defining a unique constraint on a table variable? Won't the 'optimized assuming one row in a table variable' render this unnecessary because the optimizer will consider the value in the column is going to be unique anyway?

    Comment: I've noticed that when more than one multi-row table variable is involved in a query the optimizer tends to table scan one and repeatedly table scan the other. This can get really expensive in CPU and is best avoided.

    Overall though this sheds a lot of light on a really tricky area. Thanks again.

    Tim

    Edit: Rephrased the question!

    .

  • memjm2003

    SSC Rookie

    Points: 44

    Excellent article !! Just a question ... what about tables define like

    WITH A1 (Col1, Col2, Col3) as

    (select Col11, Col12, Col13 from MyTable where Col11=5)

    INSERT INTO ...

    ???

    What kind of table is it ?? And what about it's performance ?? Thanks. Mary.

  • WayneS

    SSC Guru

    Points: 95373

    memjm2003 (6/10/2009)


    Excellent article !! Just a question ... what about tables define like

    WITH A1 (Col1, Col2, Col3) as

    (select Col11, Col12, Col13 from MyTable where Col11=5)

    INSERT INTO ...

    ???

    What kind of table is it ?? And what about it's performance ?? Thanks. Mary.

    This is a common-table-expression. It can be thought of as a pre-defined sub-query, or a derived table. As such, it maintains all of the performance that it's underlying tables support with their indexes, etc.

    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

  • Nick Beagley

    Ten Centuries

    Points: 1239

    There's also the issue that you won't get a parallel query plan if you modify a table variable, but temp tables are fine:

    From SQL2008 BOL: http://msdn.microsoft.com/en-us/library/ms175010.aspx

    Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead. For more information, see CREATE TABLE (Transact-SQL). Queries that read table variables without modifying them can still be parallelized.

  • antonio.collins

    SSCrazy Eights

    Points: 9600

    this is probably the best write-up of table variables i've seen and one of the best articles on SSC. kudos.

    :w00t:

Viewing 15 posts - 16 through 30 (of 164 total)

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