...and everything happens in memory instead of a combination of memory and disk.
...while a temporary table exists until the server is brought down and also it uses lot of disk space than a derived table in the temdb database.
You wrote a very good introduction to the use of Derived Tables... but you really need to check your resources on the two statements above....
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
Change is inevitable... Change for the better is not.