Common Table Expression in SQL Server 2005
Common Table Expression or CTE is a new concept introduced in SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query. A CTE is similar to a derived table in that it is not stored as an object permanently and exists only for the duration of the query. Unlike a derived table, CTEs can be defined just once, yet appear multiple times in the subsequent query.
When to use CTE:
· Create a recursive query.
· Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
· Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
· Reference the resulting table multiple times in the same statement.
The basic syntax structure for a CTE is:
WITH cte_name (optional column list) AS
(
Sql Statement;
)
Sql statement that uses the above CTE
In the following example the CTE is referenced two times: one time to return the selected columns for the salesperson, and again to retrieve similar details for the salesperson's manager. The CTE expression Sales_CTE has three columns (SalesPersonID, NumberOfOrders, and MaxDate) and is defined as the total number of sales orders and the most recent sales order date in the SalesOrderHeader table for each salesperson.
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
FROM Sales.SalesOrderHeader
GROUP BY SalesPersonID
)
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
JOIN Sales_CTE AS OS
ON E.EmployeeID = OS.SalesPersonID
LEFT OUTER JOIN Sales_CTE AS OM
ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID; |