Post reply

Comparing Table Variables with Temporary Tables

  • June 10, 2009 at 8:43 am

    #1007457

    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.

  • June 10, 2009 at 9:16 am

    #1007479

    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

  • June 10, 2009 at 9:28 am

    #1007489

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


    My blog: http://jahaines.blogspot.com

  • June 10, 2009 at 9:35 am

    #1007497

    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

  • June 10, 2009 at 1:24 pm

    #1007645

    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.

  • June 10, 2009 at 1:56 pm

    #1007653

    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 !

  • June 10, 2009 at 2:05 pm

    #1007660

    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

  • June 10, 2009 at 2:18 pm

    #1007664

    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

  • June 10, 2009 at 2:32 pm

    #1007669

    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

  • June 10, 2009 at 4:06 pm

    #1007694

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

    Thank you..

  • June 10, 2009 at 4:31 pm

    #1007700

    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!

    .

  • June 10, 2009 at 6:27 pm

    #1007728

    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.

  • June 10, 2009 at 6:43 pm

    #1007733

    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

  • June 10, 2009 at 7:36 pm

    #1007741

    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.

  • June 10, 2009 at 7:40 pm

    #1007742

    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 163 total)

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