SQL - Derived Tables

  • Good article, but I'd have included using derived tables to replace cursors and other use cases just as or more useful

  • Very interesting technique, I can see that coming in handy. I really need to read more. 😉

    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • I've Recently been working with derived and temporary tables and in most instances I've found the performance issues to be neglible. To be fair I'm using relatively small data sets.

    But I find by using temporary tables I can make the SQL scripts much easier to interprete at a later date especially when there are multiple, complicated derived tables being used and various analyst using the scripts.

    Surely in these instances it's down to personal preference

  • This is a great article and a technique overlooked often for increasing performance.

    After reading the article I had a few things that I was going to add but I read Jeff's reply and sense it was the same as mine I'll quote him 😉

    First, Derived Tables are NOT memory only constructs... just like any query, if they run out of room in memory, they WILL make a thing called a "Working" table and you can frequently see them in the text version of execution plans. Guess where those bad boys live... you guessed it... memory if they fit and if they don't, TEMPDB!

    Also, your information about Temp Tables living only on disk is dead wrong. Please read the following Microsoft provided URL...


    ... and pay particular attention to where it states "If memory is available, both table variables and temporary tables are created and processed while in memory (data cache). "

    You also need to check Books Online for what the persistance of a Temp Table actually is... you DON'T need to bring the server down to get rid of a Temp Table.

    Don't let this dampen your writing spirit... you provided a really good intro to derived tables... I just had to correct your statements about temp tables. In fact, there are some occasions where a Temp Table will blow the doors off of derived tables... usually in a multi-table-joined update when aggragates are necessary. They work pretty well as a substitute for CTE's, too!

    --Jeff Moden

    Another thing to be careful of is indexing and statistics. You have to keep in mind things will change when you change the structure of your query like derived tables. Run the execution plan after doing so and make sure you are getting the full speed out of SQL Server by the means of supporting objects. Also don't forget to plan for adding indexes and stats to support the query alterations. Maintenance jobs that have already been written should be updated to reflect

    Well done Prashant!

  • tony rogerson (1/16/2008)

    The derived table itself is just expanded into the main query (just like a view) this means that there are no statistics available for the derived table.


    I'm not sure that's true... it's true that neither a view nor a derived table will have statistics on them, but the underlying data data does and that's how it selects which indexes on the underlying tables to use in the execution plan.

    --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 5 posts - 31 through 34 (of 34 total)

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