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

    http://support.microsoft.com/default.aspx?scid=kb;en-us;305977&Product=sql2k

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