Temp Table 'vs' Table Variable 'vs' CTE.

  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Hi All,

    I wants to clear my concept about Temp Table,

    Table Variable & Common Table Expression Concept.

    Can anybody please clear me these three concepts

    and when should i use this?

    Also I wants to know how they are different from

    each other in the terms of executing in SQL Server.

    Cheers!

    Sandy.

    --

  • Gail Shaw

    SSC Guru

    Points: 1004446

    I'll give this a try.

    Temp tables

    Behave just like normal tables, but are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that that proc calls.

    Just like normal tables, they can have primary keys, constraints and indexes, and column statistics are kept for the table.

    Temp tables, while they have space assigned to them in the tempDB database, will generally be accessed only from memory, unless the server is under memory pressure, or the amount of data in the table is large.

    Table Variables

    These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

    Like with temp tables, table variables reside in TempDB. they have entries in the system tables in tempDB, just like temp tables, and they follow the same behaviour regarding whether they are in memory or on disk.

    Table variables can have a primary key, but indexes cannot be created on them, neither are statistics maintained on the columns. This makes table variables less optimal for large numbers of rows, as the optimiser has no way of knowing the number of rows in the table variable.

    CTE

    In my experience, CTEs are more like temporary views than anything else. When you look at the execution plan, you'll see that they are inlined into the query, not materialised and stored. I find, with the exception of recursion, they're more to make queries simpler to write than faster to run.

    Does that help?

    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
  • VaiydeyanathanVS

    Hall of Fame

    Points: 3053

    Sandy,

    Check out these examples and try to build your own functionality....

    http://www.lakesidesql.com/articles/?p=12

  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Hey Gail Shaw,

    Thanks a lot,

    Can you please give more Idea about CTE,

    and also its uses in SQL Server 2005.

    Because its a HOT Topic (CTE) in SQL Server 2005.

    VAIYDEYANATHAN.V.S,

    Thanks for link, but Less information about CTE.

    Can you please give some nice link for CTE?

    Cheers!

    Sandy

    --

  • Gail Shaw

    SSC Guru

    Points: 1004446

    What more do you want to know on CTEs? Here's a good, albeit fairly technical, article on CTEs.

    http://blogs.msdn.com/craigfr/archive/2007/10/18/ctes-common-table-expressions.aspx

    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
  • Andras Belokosztolszki

    SSC-Insane

    Points: 21971

    http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b305977&Product=sql2k also contrasts the advantages/disadvantages of table variables and temp tables.

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Jeff Moden

    SSC Guru

    Points: 994874

    Gail... nice explanation! Short, sweet, and to the point.

    Sandy... think of a CTE the same as you would any "inline view" or "derived table" except that they are cited before the SELECT instead of in the FROM clause of a SELECT. Big advantage of CTE's is you can reference them more than once in the same SELECT.

    Big advantage of a Temp table even over CTE's is that, once built, they can be referenced by multiple queries in the same proc... CTE's can only be referenced by the current query they are built for... just like derived tables.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • Grant Fritchey

    SSC Guru

    Points: 395586

    Everyone else has covered this for the most part, but I don't mind adding one bit of information. Table variables, unlike temporary tables, do not have statistics created on them. This means that they are treated as if they have one row for any joins or searches done against them. When they only have a few rows, this doesn't matter, but when they have hundreds or thousands of rows, it makes a huge difference in the execution plans and the performance. It's just something to keep in mind.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Hey Jeff & Gail,

    Gail As per you

    :

    These tables behave very much like other variables in their scoping rules. They are created when they are declared and are dropped when they go out of scope. They cannot be explicitly dropped.

    And as per Jeff

    :

    CTE the same as you would any "inline view" or "derived table" except that they are cited before the SELECT instead of in the FROM clause of a SELECT.

    I wants to know If I will go for a "Select" Query for both what type of operation will going on in SQL Server?

    As per Gail said that Table variable can't be with index and

    CTE also, so Is it always go for table scan or not ?

    Will be this an issue on my performance?

    Because these both are not following the index scan concept.

    Cheers!

    Sandy.

    --

  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Hey Grant,

    Can you have a look on to my last post..

    I will be happy if you and jeff will have a

    sharp eye on my post.

    Thanks a lot for your reply.

    Cheers!

    Sandy.

    --

  • Kishore.P

    SSCrazy Eights

    Points: 8147

    Check the following link for FYI:

    http://www.sql-server-performance.com/articles/per/temp_tables_vs_variables_p1.aspx

    🙂

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Common Table Expressions, despite their name, are not tables. Think of them as a temporary view that you can use to simplify a query. The 'temporary view' lasts for just one statement.

    The following two queries are equivalent (and have the same execution plan)

    Based off AdventureWorks

    Without a CTE

    SELECT SalesOrderNumber, PurchaseOrderNumber, TotalPerOrder FROM Sales.SalesOrderHeader soh INNER JOIN

    (SELECT COUNT (*) TotalPerOrder, SalesOrderID FROM Sales.SalesOrderDetail sd GROUP BY sd.SalesOrderID) Sub

    ON soh.SalesOrderID = sub.SalesOrderID

    With a CTE

    ;WITH Sub (totalPerOrder, SalesOrderID) AS (

    SELECT COUNT (*) TotalPerOrder, SalesOrderID FROM Sales.SalesOrderDetail sd GROUP BY sd.SalesOrderID

    )

    SELECT SalesOrderNumber, PurchaseOrderNumber, TotalPerOrder

    FROM Sales.SalesOrderHeader soh INNER JOIN Sub ON soh.

    What I did was to take the subquery in the first, and turn it into a CTE in the second.

    All that's happened when the outer query executes, is that the definition of the CTE is placed into the query, replacing reference to the CTE, essentially becomming an inline subquery

    There's no temp table of any form involved, hence no concerns regarding indexing or table scans.

    Run those 2 in adventureworks with the execution plan on, and you'll see how they run.

    Sandy (10/29/2007)


    Hey Grant,

    I will be happy if you and jeff will have a

    sharp eye on my post.

    So I needn't have replied?

    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
  • IN_Sandeep

    SSCertifiable

    Points: 6259

    Hey Gail,:hehe:

    I apology for that...:D

    I didn't mean that.(he he he..lolz)

    Actually Grant is one of my Fav member in this site.

    I can say this you are awesome, when you are replying me,

    and you are different than others.....:)

    I will be here tomoro to discuss more about this topic to make myself clear till I am not sure, I am not going to leave you people...

    Still I am not sure about Table Variable and CTE....wrt to Table Scan or Index Scan......

    Cheers!:P

    Sandy.

    --

  • Grant Fritchey

    SSC Guru

    Points: 395586

    Sandy (10/29/2007)


    Hey Grant,

    Can you have a look on to my last post..

    I will be happy if you and jeff will have a

    sharp eye on my post.

    Thanks a lot for your reply.

    Cheers!

    Sandy.

    Hey Sandy,

    Don't limit your options, Gail is better at this stuff than I am.

    You need to think about these as different things with different functions. CTE is an extremely neat and clean way to create a multi-use derived table, a temporary view as others defined it. Table variables and temporary tables are methods for persisting data temporarily. Neither directly assists in querying the way a CTE does. Usually they're used when you need to some sort of multi-step manipulation or share data between processes or something along those lines. But again, when the time comes to query the data out of a table variable or temp table, there are very distinct differences in behavior that, depending on the amount of data, makes temp tables perform better than table variables.

    Cheers.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Gail Shaw

    SSC Guru

    Points: 1004446

    Sandy (10/29/2007)


    Hey Gail,

    I apology for that...:D

    I didn't mean that.(he he he..lolz)

    Is fine. I was fairly sure you didn't 😉

    Actually Grant is one of my Fav member in this site.

    Oooh, Grant, you've got a fan... 😀

    I will be here tomoro to discuss more about this topic to make myself clear till I am not sure, I am not going to leave you people...

    Still I am not sure about Table Variable and CTE....wrt to Table Scan or Index Scan......

    Cheers!:P

    Sandy.

    We'll be here.

    As for CTEs and index/table scans. As I said, CTEs aren't tables. They're temporary views. As such, they them selves cannot be the subject of index or table scans. The tables referenced by the CTE are the ones that are read (either by scan or seek)

    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

Viewing 15 posts - 1 through 15 (of 51 total)

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