SQL Server Derived Tables
Some result sets are difficult to extract from a database without performing multiple queries or using temporary tables and cursors, which can harm performance. Derived tables help with some problems by creating virtual tables within the scope of a query.
What is a Derived Table?
When you need to obtain complex result sets from a SQL Server database, particularly when using multiple aggregation functions, simple queries may not be effective. You may resort to creating temporary tables, filling them with data using several queries and inserts, and using cursors to loop through the temporary data making updates. Finally, you would execute a query against the temporary table to obtain the final results.
An alternative approach for some data problems is to use derived tables, also known as inline views. A derived table is a virtual table that is created within the scope of a query. The table is created with a SELECT statement of its own and is given an alias using the AS clause. It's contents can then be used within the query but become unavailable once the query has completed. The result is similar to when you use a temporary table but does not have the overhead of creating and dropping the temporary table and filling it with data using INSERT and UPDATE statements. You can also think of a derived table as a temporary view, which does not need to be defined in the database beforehand.
Derived tables do not allow you to create result sets that could not be generated in another manner. They do increase the readability of queries and stored procedures in some cases and can eliminate the requirement to use cursors and temporary tables. As the use of temporary tables generally requires an overhead of table creation and dropping, and of write operations to populate the data, derived tables can improve the performance of some queries. However, derived tables are not indexed, as temporary tables and views can be, so you should try several approaches to find the correct balance of maintainability, performance and efficiency for your situation.
To demonstrate the use of derived tables we'll need a table that's populated with data. We'll use a very simple table containing sales data. Each row represents a sale, with a value, made by a salesperson who has an area code. In a real system you would probably normalise the schema, adding Salespeople and Areas tables, with foreign keys linking the three tables together. For our examples this complication is unnecessary.
CREATE TABLE Sales
INSERT INTO Sales VALUES ('Bob', 'N', 100)
INSERT INTO Sales VALUES ('Bob', 'N', 125)
INSERT INTO Sales VALUES ('Sam', 'N', 120)
INSERT INTO Sales VALUES ('Jim', 'S', 120)
INSERT INTO Sales VALUES ('Tim', 'S', 130)
Creating a Derived Table
Derived tables are used in place of true tables in a query. Where you would normally introduce a table name in the FROM clause of your query, you can substitute another query, defined in parentheses and named with an AS clause. You can use the data from this inner query in the column list, in joins and in other areas where table data would be acceptable. So that the results are accessible, you must ensure that every column created by the derived table has a name.
A very simple example is shown below. Here the derived table selects all of the rows from the Sales table. The resultant inline view is named, "DerivedSales". There are two columns in the derived table, one combines the salesperson's name and area and the other holds the sale value. Note that the first column is given the alias, "SalesDetail". Without this name the query would fail. The "outer" query retrieves all of the rows from the derived table that have a sales value greater than 100.
SELECT * FROM
SELECT Salesperson + ' (' + Area + ')' AS SalesDetail, Value FROM Sales
) AS DerivedSales
WHERE Value > 100
Bob (N) 125.00
Sam (N) 120.00
Jim (S) 120.00
Tim (S) 130.00
The above example is very simplistic and the results could easily be obtained with a single query. It's included in the article to demonstrate the syntax used for derived tables. In the remainder of the article we'll create a more complex query that combines two derived tables, each containing aggregations based upon the Sales data but grouped in different ways.
Before we create the full query, let's look at the SELECT statements that we'll be using to form the derived tables. The first of these obtains a list of all of the areas in the Sales data. For each area, the total number and value of sales is aggregated and grouped. The query and its results are shown below:
COUNT(Value) AS NumAreaSales,
SUM(Value) AS TotalAreaSales
Area NumAreaSales TotalAreaSales
---- ------------ --------------
N 3 345.00
S 2 250.00
The second derived table is similar to the first. Here we again aggregate the sales but this time grouping by salesperson. The area is included in the results as we will be joining the two derived tables based upon that column.
COUNT(Value) AS NumSalesPersonSales,
SUM(Value) AS TotalSalesPersonSales
Salesperson Area NumSalesPersonSales TotalSalesPersonSales
----------- ---- ------------------- ---------------------
Bob N 2 225.00
Sam N 1 120.00
Jim S 1 120.00
Tim S 1 130.00
14 June 2012