I want to know about derived tables in sql server 2005

Hi Sukeshiph,

A derived table is a virtual table that's created and populated on the fly from a select statement.

In concept this is similar to creating a temporary table and then using the temporary table in your query, but the approach is much simpler, coz it can be done in one step. This can be tremendously useful in certain situations.

Some times derived tables can be most useful, for instance you need to create a view for a single query and then you want to use it within another query and after that the view should be dropped because nothing else uses this view, in this context derived tables suit you best and benefits you by saving to create you a catalog entry in the form of view in the server.

For repeated queries, a SQL derived table used multiple times performs well with cached definition. It will not degrade your performance. A SQL derived table differs from a temporary table in that a SQL derived table exists only for the duration of the query, while a temporary table exists until the SQL Server are recycled or the temporary table is dropped manually and also it uses lots of disk space than a derived table in the temdb database.

So it's better to make use of Derived tables wherever possible by eliminating the rearely used or one time only views and by eliminating the temporary tables.

You would gain a lot of performance gain using a derived table when compared to using a temparory table as illustrated below:

Steps needed to read from a temporary table:

1. Lock tempdb database
2. CREATE the temporary table (write activity)
3. SELECT data & INSERT data (read & write activity)
4. SELECT data from temporary table and permanent table(s) (read activity)
5. DROP TABLE (write activity)
6. Release the locks

Compare this with the number of steps required for a derived table:

1. CREATE locks, unless "read uncommitted" isolation level is specified.
2. SELECT data (read activity)
3. Release the locks


Thus derived tables require less I/O and less disk space.

Hope this was helpful.

The question looks to be abandoned by the user who asked it. If no action is taken within 2 days, a Quomon Moderator will consider closing the question and distributing the points.

The Quomon Team