Post reply

Difference between temp table, table variable, derived table and CTE

  • April 23, 2011 at 10:36 am

    #249756

    Hi,

    Please let me know difference between temp table, table variable, derived table and CTE.

    Thanks in advance.

  • April 23, 2011 at 10:53 am

    #1315142

    Did you try google?

    Temp tables and table variables are actual tables, both in TempDB

    CTE and derived tables are both named resultsets used in queries, they're not (necessarily) stored in TempDB, they're just portions of a query.

    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

  • April 23, 2011 at 11:43 am

    #1315150

    This is a pretty decent article for Comparing Table Variables to Temporary Tables[/url].

    You can think of a CTE as a predefined sub-query, where it is defined just once instead of for every time it is needed, and it is called by the cte alias that you give.

    A derived table is essentially a sub-query used in the from clause of a select statement.

    Example of a CTE:

    ;WITH CTE (ColumnAlias) AS

    (

    SELECT 1

    )

    SELECT c1.ColumnAlias

    FROM CTE c1

    CROSS JOIN CTE c2;

    Example of a derived table:

    SELECT *

    FROM (SELECT Account, MAX(date) FROM Table) DerivedTable;

    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

  • April 23, 2011 at 4:00 pm

    #1315204

    Gosh, if that didn't sound like an interview question, I don't know what does.

    --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.

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

Viewing 4 posts - 1 through 3 (of 3 total)

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