ALTUNTERİM' in klavyesi..

Recursive and Non-recursive Common Table Expressions (CTEs)

You can think of CTEs as a simple yet more powerful alternative to derived tables. In some cases, CTEs may be used in places where you are currently using temporary tables, table variables, or views. The three important motivations for introducing CTEs in SQL Server 2005 are recursion; to provide alternative, simplified, readable, and manageable syntax for complex SQL statements, possibly making use of derived tables; and ANSI SQL-99 compliance (CTEs are defined in SQL-99).

A CTE can be defined as a temporary named result set, which is derived from a simple query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. It is important to note that the scope of a CTE is just the statement in which it is declared. The CTE named result set is not available after the statement in which it is declared and used.

Here is a simple example that uses a CTE to count number of direct reports for each manager in the AdventureWorks sample database:

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 * FROM DirReps ORDER BY ManagerID
GO

In the scope of a SELECT statement, this query declares a result set named DirReps, and then the outer SELECT statement retrieves everything from that result set. Nothing fancy herejust a simple example to give you a taste of CTE syntax.

The following CTE example that is a bit more involved. This query is used to trend the sales data; for every year, it gets the total sales amount and compares it with the previous year’s sales:

WITH YearlyOrderAmtCTE(OrderYear, TotalAmount)
AS
(
     SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)
     FROM Sales.SalesOrderHeader AS H JOIN Sales.SalesOrderDetail AS D
          ON H.SalesOrderID = D.SalesOrderID
     GROUP BY YEAR(OrderDate)
),
SalesTrendCTE(OrderYear, Amount, AmtYearBefore, AmtDifference, DiffPerc)
AS
(
 SELECT thisYear.OrderYear, thisYear.TotalAmount,
        lastYear.TotalAmount,
        thisYear.TotalAmount - lastYear.TotalAmount,
        (thisYear.TotalAmount/lastYear.TotalAmount - 1) * 100
 FROM YearlyOrderAmtCTE AS thisYear
      LEFT OUTER JOIN YearlyOrderAmtCTE AS lastYear
      ON thisYear.OrderYear = lastYear.OrderYear + 1
)
SELECT * FROM SalesTrendCTE
GO

This query essentially defines two CTEs. The first CTE, called YearlyOrderAmtCTE, groups the total sales by year. This CTE is then used in the second CTE, called SalesTrendCTE, and the outer or main query selects all the rows from SalesTrendCTE. Notice how SalesTrendCTE uses YearlyOrderAmtCTE to get the current and previous years’ total sales figures.

In the previous example, the second CTE refers to the previous CTE. If a CTE refers to itself, it is then called a recursive CTE, and this is where CTEs get interesting. Without any further ado, here is an example of a recursive CTE:

WITH MgrHierarchyCTE(EmployeeID, EmployeeName, ManagerID, Level)
AS
(
   SELECT e.EmployeeID, c.FirstName + ' ' + c.LastName, e.ManagerID, 0
   FROM HumanResources.Employee AS e
        JOIN Person.Contact AS c
        ON c.ContactID = e.ContactID
   WHERE e.EmployeeID = 111

   UNION ALL

   SELECT mgr.EmployeeID, co.FirstName + ' ' + co.LastName, mgr.ManagerID,
          Level + 1

   FROM HumanResources.Employee AS mgr
        JOIN Person.Contact AS co
        ON co.ContactID = mgr.ContactID
        JOIN MgrHierarchyCTE AS cte
        ON cte.ManagerID = mgr.EmployeeID
)
SELECT * FROM MgrHierarchyCTE;
GO

This recursive CTE example illustrates traversing up the management hierarchy all the way up to the topmost manager for the employee with the ID 111. The query returns a result set that contains a row for each manager in the hierarchy, starting with the immediate manager and going up to the top-level manager.

The first SELECT statement within the CTE definition finds the immediate manager details. This row is then combined, using UNION ALL, with another SELECT statement that self-references the CTE to traverse the management hierarchy.

The most common problem with recursion is infinite loops. SQL Server solves this problem by defining a serverwide recursion level limit setting called MAXRECURSION, which defaults to 100. You can also specify a MAXRECURSION hint in your CTE query to limit the recursion level for that query.