Common Table Expression in SQL Server 2005

One of the many new features in SQL Server 2005 are Common Table Expressions (CTEs), which provide a more readable and usable approach to derived tables.

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)



    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


    ON E.ManagerID = OM.SalesPersonID

ORDER BY E.EmployeeID;

By mohan kitusamy   Popularity  (1764 Views)