Monday, 10 May 2010

Common Table Expressions (CTEs)

Common Table Expressions (CTEs) were introduced in SQL Server 2005. They are view/derived tale-like construct.

“A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.” - http://msdn.microsoft.com/en-us/library/ms190766.aspx (MSDN SQL Server 2008 documentation)

CTEs offers the advantage of improved readability and ease in maintenance of complex queries.

The basic structure of a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition )

You can then query the CTE as you would a table/view.

Example:

USE AdventureWorks;
GO
-- Define the CTE
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)

-- Query using the CTE
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
       E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS -- Join against CTE as if it were a table
    ON E.EmployeeID = OS.SalesPersonID
    LEFT OUTER JOIN Sales_CTE AS OM -- CTE can be referenced more than once
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;
GO

You can define multiple CTEs after the WITH keyword by separating each CTE with a comma.

A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.

Lots more here: http://msdn.microsoft.com/en-us/library/ms175972.aspx