Derived Table in SQL SERVER

Introduction
With SQL Server you have the ability to create derived tables on the fly and then use these derived tables within your query. In very basic terms, a derived table is a virtual table that's calculated 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, because it can all be done in one step.

Boost Performance
The biggest benefit of using derived tables over using temporary tables is that they require fewer steps, and everything happens in memory instead of a combination of memory and disk. The fewer the steps involved, along with less I/O, the faster the performance.

Here are the steps when you use a temporary table:

Lock tempdb database
-CREATE the temporary table (write activity)
-SELECT data & INSERT data (read & write activity)
-SELECT data from temporary table and permanent table(s) (read activity)
-DROP TABLE (write activity)
-Release the locks
Compare this with the number of steps it takes for a derived table:

-CREATE locks, unless isolation level of "read uncommitted" is used
-SELECT data (read activity)
-Release the locks
As is rather obvious from this example, using derived tables instead of temporary tables reduces disk I/O and can boost performance.


example

without derived table
SELECT C.CustomerID, C.CompanyName, COUNT(O.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN Orders O ON
C.CustomerID = O.CustomerID
WHERE YEAR(O.OrderDate) = 1996
GROUP BY C.CustomerID, C.CompanyName

with derived table
SELECT C.CustomerID, C.CompanyName,
COUNT(dOrders.OrderID) AS TotalOrders
FROM Customers C LEFT OUTER JOIN
/* start our derived table */
(SELECT * FROM Orders WHERE YEAR(Orders.OrderDate) = 1996) AS dOrders
/* end our derived table */
ON
C.CustomerID = dOrders.CustomerID
GROUP BY C.CustomerID, C.CompanyName

Updating a derived table
Now the question of updating a derived table. I don't think that it is a big issue, but still it's a question asked about derived tables. So the answer is as follows,

- MS SQL 2000 - Not possible

- MS SQL 2005 - Possible

So, updating a derived table is not possible in SQL Server 2000. If you try to do so, then SQL Server will give you the following error.

Update T SET Id=0 FROM (SELECT * FROM tt1) AS T
Error:
Server: Msg 4421, Level 16, State 1, Line 1
Derived table 'T' is not updatable because a column of the derived table is derived or constant.

To Know more please visit: http://www.sqlservercentral.com/articles/DerivedTables/61388/