SQL Server 2005 Common Table Expression (CTE)   

 

 

CTE’s are similar to SQL Server 2000 implementations of  derived tables and temporary tables in the sense that it creates a temporary resultset on the fly. It also works like a temporary view defined within the scope of an executing statement.

 

 

Benefits of SQL server CTE’s

-          Can be used for recursive Query

-          Meets the ANSI SQL standard

-          It is much easier to code

-          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.

 

 

A look back on temporary tables and derived tables

 

An example of a Derived Table

 

use adventureworks

Select b.* from

(

   SELECT ListPrice, ListPrice * .95 as markedup

   FROM Production.Product

) as b

 

 

An example of Temporary tables

 CREATE TABLE #MyCTE

(

  ListPrice money,

  SellPrice money

)

INSERT INTO #MyCTE

            (ListPrice, SellPrice)

            SELECT ListPrice, ListPrice * .95

            FROM Production.Product 

 

Limitations of derived tables and temporary tables

 

-          Derived tables cannot be referenced or used more than once

-          Temporary tables cannot reference it self thought it can be used or referenced

more than once.

 

 

 

 

 

A look at some of CTE’s feature

 

A Simple CTE – more looks like a derived table

 

USE AdventureWorks;

GO

WITH DirReps(ManagerID, DirectReports) AS

(

    SELECT ManagerID, COUNT(*)

    FROM HumanResources.Employee AS e

    WHERE ManagerID IS NOT NULL

    GROUP BY ManagerID

)

SELECT ManagerID, DirectReports

FROM DirReps

ORDER BY ManagerID;

GO

 

Using a common table expression to limit counts and report averages

 

WITH DirReps (Manager, DirectReports) AS

(

    SELECT ManagerID, COUNT(*) AS DirectReports

    FROM HumanResources.Employee

    GROUP BY ManagerID

)

SELECT AVG(DirectReports) AS [Average Number of Direct Reports]

FROM DirReps

WHERE DirectReports>= 2 ;

GO

 

Referencing a common table expression more than one time

            USE AdventureWorks;

GO

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;

GO

 

Using a recursive common table expression to display multiple levels of recursion

 

 

USE AdventureWorks;

GO

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS

(

    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

    FROM HumanResources.Employee

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1

    FROM HumanResources.Employee e

        INNER JOIN DirectReports d

        ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, EmployeeLevel

FROM DirectReports ;

GO

 

 

 

Using a recursive common table expression to display two levels of recursion

 

 

USE AdventureWorks;

GO

WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS

(

    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel

    FROM HumanResources.Employee

    WHERE ManagerID IS NULL

    UNION ALL

    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1

    FROM HumanResources.Employee e

        INNER JOIN DirectReports d

        ON e.ManagerID = d.EmployeeID

)

SELECT ManagerID, EmployeeID, EmployeeLevel

FROM DirectReports

WHERE EmployeeLevel <= 2 ;

GO