Post reply

Question about when to use different types of tables

  • February 19, 2008 at 2:18 pm

    #182345

    Does anyone know of a good article which compares and tells when to use derived tables, common table expressions, table variables and temp tables? It is nice to have a wide variety, but I'm interested in when to use which.

    Thanks.

  • February 19, 2008 at 8:48 pm

    #780053

    I don't know about comprehensive articles, but I'll put my two cents in.

    Derived Tables

    I primarily use these for "clean looking" SQL code. Easy to modify and easy to refactor. Especially when I have to repeat the same calculation more than once, I'll create a derived table.

    CTE's can do the same thing as derived tables, except the definition of the inner table is above the rest of the query with the additional benefit of recursive queries. It's just my personal preference, but I don't use CTEs unless I need a new feature they provide.

    Table variables vs. Temp tables.

    While very similar, there are some key aspects that can make one or the other unusable depending on your circumstance.

    Table Variables:

    1. Can only be used in scope in which they are declared

    2. Can be recipient of Table Valued Function

    Temp Tables

    1. can be used outside of declared scope. In other words, if I have stored proc A which creates a temp table, and calls Proc B, Proc B can perform any operation on the temp table that A created.

    2. Indexes...someone correct me since I'm not looking at my docs. I think you can create an index on a temp table, but not a table variable.

    3. Are persisted in tempdb

    There are also subtle performance differences between temp tables and table variables, so if the choice is ambiguous, try your proc using both, and see which one performs better. From what I remember, table variables perform better with smaller amounts of records.

    Also, a brief search reveals this article: http://databases.aspfaq.com/database/should-i-use-a-temp-table-or-a-table-variable.html

    which seems to be much more in depth than what I'm giving you here.

    Hope this helps.

  • February 20, 2008 at 4:05 am

    #780135

    Thanks for your input. I'll check out that article.

  • February 20, 2008 at 6:08 am

    #780160

    Well, first, seperate derived tables & CTE's from temp tables & table variables. They are totally different critters. Derived tables & CTE's are constructs that exist within a query statement for the amount of time it takes that query statement to run. Temporary tables and table variables are a way of, temporarily, persisting data across statements and even across procedures or connections. Deciding which you want when is where things get hairy. As a general rule, set based operations, which is the strength of TSQL, means that you'll use temporary tables & table variables sparingly, if at all. I recommend you pick up Itzik Ben Gan's book, Inside TSQL Querying. It'll get you further down the road than a simple post online will.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 20, 2008 at 9:09 am

    #780256

    I wrote a short comparison of temp tables and table variables a while back. - Temp tables vs table variables[/url]

    Might be of some use.

    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

  • February 20, 2008 at 9:12 am

    #780259

    Jeremy (2/19/2008)

    Temp Tables

    ...

    3. Are persisted in tempdb

    So are table variables. Both will reside only in memory unless there is memory pressure, or the number of rows becomes too large to store in memory. They they'll be flushed to disk in tempDB.

    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

  • February 20, 2008 at 10:51 am

    #780315

    Thanks for your input. I'll be sure to read your article.

Viewing 7 posts - 1 through 6 (of 6 total)

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