Common Table Expressions (CTE)

CTEs are one of the beautiful and the most powerful feature of SQL Server. It is introduced in SQL Server 2005 and I guess is one of the best things in SQL Server. This not only simplifies most of the complex and impossible queries in T-SQL, but also provides one medium to deal with recursive queries. Moreover, I can say it is mainly for recursive queries. It can be used in replacement of derived tables (sub queries), temp tables, table variables, inline user-defined functions etc.

The Common Table Expression (CTE) is called as a temporary named RESULT SET, within the scope of an executing statement that can be used within a SELECT, INSERT, UPDATE, or DELETE or even in a CREATE VIEW or MERGE statement.

CTE Syntax:

[ WITH <common_table_expression_name>]

[ ( column_name [ ,…n ] ) ]

AS

( CTE_query_definition ),…n

• < common_table_expression_name> : name of the CTE, used as a identifier. This should be different from any other CTE defined within the same “WITH” clause. The expression name can be same as the name of the base table or view (any reference to the expression name will use the CTE and not the base table).
• [ ( column_name [ ,…n ] ) ]: Represents the column names for the defined CTE (Optional if there is no duplicity in column names in the result set of the CTE_query_definition). And if the column names are defined with the CTE, then the number of columns should match with the inner result set’s columns.
• ( CTE_query_definition ): This specifies a SELECT statement whose result set populates the common table expression. A CTE cannot define another CTE inside it and the SELECT statement must meet the same criteria as for creating a view.

More than one CTEs can be created within one “WITH” statement, but scope of all those CTEs will be limited to the immediate t-sql statement after “WITH” (described below in detail).

Stored procedures are mainly used to deal with complex logics either for security purpose or to accomplish some combined tasks, Or in most of the cases we use temporary tables or table variables to keep intermediate results which at the end tempt to be deleted when not in use. But among all these, CTE is the best as it act as a normal view only.

Microsoft offers the following four advantages of CTEs:

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

Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

Simple CTE Example: To get the employee information from the ‘employee’ table.

WITH emp_CTE as (

            select EmpName, empEmailId, salary

            from dbo.employee)

 select * from emp_CTE;

 This is just to show how CTEs are working. Let’s take another example, where we want information from employee along with departmental information and then want to do some operations on it.

WITH emp_CTE as (

            select EmpName, empEmailId, salary, DeptName, DeptStatus

            from dbo.employee A

            inner join

            dbo.Department D on A.DeptID = D.DeptID )

  select * from emp_CTE where DeptStatus = 'Active';

This is yet another simple query, where one CTE named “emp_CTE” is defined to keep information about employees along with departmental information.

Some more CTEs examples:

WITH   CTE1(CL11,CL22)  as   (select 1 as CL1, 3 as CL2)

        ,CTE2(CL3,cl5,cl6)    as    (select 1,5,7)

select * from CTE1 inner join CTE2 on CL11 = CL3

Here we defined two CTEs (i.e. CTE1 and CTE2) within the same “WITH” statement, and both these CTEs are used in the immediate t-sql statement.

CTEs with Union ALL

WITH CTE_union(CL1,CL2) AS    (

            SELECT 1 AS CL1, 3 AS CL2

            UNION ALL

            SELECT 5 as CL11,7 as CL22)

Select * from CTE_union

CTEs are very useful for on the fly IDENTITY columns:

SQL Server supports two types of Common Table Expressions (CTEs):

• Recursive CTEs: Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages. This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc.
• Non- Recursive CTEs: These are the other complex queries that can be simplified with the help of CTEs like using cursors, getting sequence numbers, filtrations, etc.

Recursive CTEs: The Recursive CTEs must have two types of query definition members:

• Recursive Member: If the query definition references the CTE itself, then it is called Recursive member. This is defined at the last i.e. after defining all the anchor members.
• Anchor Member: All other CTE query definitions without reference of CTE are Anchor Members. All anchor member query definitions must be put before the first recursive member definition.

The following are the restrictions to Recursive CTEs:

• In Recursive CTEs, it’s not possible to have multiple query definitions (i.e. multiple CTEs [, separated]) within a single “WITH” statement.
• All anchor members supports UNION ALL, UNION, INTERSECT, or EXCEPT for combining the anchor definition. But only UNION ALL is supported for combining the last anchor member and the first recursive member and even for multiple recursive members.
• The total number of columns and the data types of those columns must match for the anchor member and the recursive member. (Note: data type should be same for recursive member and the corresponding anchor member)
• CTE expression_name can be referenced only once in the FROM clause of a recursive member.

Recursive Common Table Expression : These are very useful in case of hierarchical data, because the CTE Query continues to execute till it reaches its lower granular level.

Basically any recursive CTE consists of three elements:

1. First invocation of the CTE: In first invocation, all the members of the CTE will be executed i.e. both the anchor members and the recursive members. And as we have CTE referenced in the recursive members, it will automatically invoke the CTE for further iterations and thus called as recursive mode or recursive invocation. In this case Anchor member’s result set will be considered as the referenced data for the CTE. As in the above figure, in the first invocation, r₀ is the value for the Anchor query and hence become the data for the CTE when referenced from the Recursive query.
2. Recursive invocation of the CTE: In all recursive invocation of the CTE, only recursive members will be executed and the anchor members will remain idle. And the data for the CTE referenced will be the result set built from the last call of the CTE. Elaborated briefly below.
3. Recursive Termination Condition: Well practically, we don’t have any control on the termination of the Recursive CTEs. I mean it is implicit; it will stop itself when no rows are returned from the previous invocation. Hence, the control condition can be applied indirectly i.e. the query’s result set can be limited with the help of filter conditions.

In order to avoid error we can use MAXRECURSION hint in the OPTION clause of the outer query where the CTE is referenced. We can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767.

CTE Example for SubQuery : In below example shows select with subquery

SELECT * FROM  (

        SELECT A.Address, E.Name, E.Age From Address A

        Inner join Employee E on E.EID = A.EID) T

WHERE T.Age > 50

ORDER BY T.NAME

Then how we can convert it into cte expression query. Following is the answer for this.

With T1(Address, Name, Age)  --Column names for Temporary table

AS

(

   SELECT A.Address, E.Name, E.Age from Address A

   INNER JOIN EMP E ON E.EID = A.EID

),

T2(Name, Desig)

AS

(

  SELECT NAME, DESIG FROM Designation)

  SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table

WHERE T1.Age > 50 AND T1.Name = T2.Name

ORDER BY T1.NAME

Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.CTE is the best as it act as a normal view only.

CTE is just shorthand for a query or subquery.
CTE sometimes call them in-line views, can make things look clearer.

The cte isn't stored anywhere as its scope is transient (the length of the statement in which it occurs) However it usage can result in tempdb usage if the generated resultset(s) are large (or complex) enough Tempdb is used to store CTE values( intermediate query result) when it uses spool operator. Spooling will happen mainly if the CTE is recursive:

A CTE is a "temporary result set" that exists only within the scope of a single SQL statement. It allows access to functionality within that single SQL statement that was previously only available through use of functions, temp tables, cursors, and so on.

Syntax

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

More than one CTEs can be created within one “WITH” statement, but scope of all those CTEs will be limited to the immediate t-sql statement after “WITH”

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

SQL Server supports two types of Common Table Expressions (CTEs):

Recursive CTEs: Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages. This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc.
Non- Recursive CTEs: These are the other complex queries that can be simplified with the help of CTEs like using cursors, getting sequence numbers, filtrations, etc.

Guidelines for Creating and Using Common Table Expressions

• A CTE must be followed by a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement that references some or all the CTE columns. A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
• Multiple CTE query definitions can be defined in a nonrecursive CTE. The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
• A CTE can reference itself and previously defined CTEs in the same WITH clause. Forward referencing is not allowed.

• Specifying more than one WITH clause in a CTE is not allowed. For example, if a CTEquerydefinition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.

• The following clauses cannot be used in the CTEquerydefinition:

  • COMPUTE or COMPUTE BY
  • ORDER BY (except when a TOP clause is specified)
  • INTO
  • OPTION clause with query hints
  • FOR XML
  • FOR BROWSE

• When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.

• A query referencing a CTE can be used to define a cursor.
• Tables on remote servers can be referenced in the CTE.
• When executing a CTE, any hints that reference a CTE may conflict with other hints that are discovered when the CTE accesses its underlying tables, in the same manner as hints that reference views in queries. When this occurs, the query returns an error. For more information, see View Resolution.
• When a CTE is the target of an UPDATE statement, all references to the CTE in the statement must match. For example, if the CTE is assigned an alias in the FROM clause, the alias must be used for all other references to the CTE. Ambiguous CTE references may produce unexpected join behavior and unintended query results. For more information, see UPDATE (Transact-SQL).

Common Table Expression (CTE) simple example

WITH [CTE Example] (Id, Title)
AS
(
  SELECT EmployeeID, Title FROM HumanResources.Employee
 )
 SELECT * FROM [CTE Example]

Common Table Expression (CTE) as SubSelect

WITH [CTE Stock] (ProductID, [Total Quantity])
As
(
 Select ProductID, Sum(Quantity)
 From Production.ProductInventory
 Group By ProductID
)
Select
 p.ProductNumber,
 p.Name,
 cte.[Total Quantity]
From Production.Product p
Inner Join [CTE Stock] cte
 On cte.ProductID = p.ProductID
Order By p.ProductNumber;

Multiple CTE in SQL Server 2008

WITH [CTE Example] (Id, Title)
AS
(
 SELECT EmployeeID, Title FROM HumanResources.Employee
), CTE (Num, EmployeeName)
AS
(
 SELECT
  CTE.Id,
  ISNULL(FirstName,'') + ISNULL(MiddleName,'') + ISNULL(LastName,'')
 FROM [CTE Example] CTE
 INNER JOIN Person.Contact C
  ON CTE.Id = C.ContactID
)
select * from CTE

The following guidelines apply to defining a recursive common table expression:

• The recursive CTE definition must contain at least two CTE query definitions, an anchor member and a recursive member. Multiple anchor members and recursive members can be defined; however, all anchor member query definitions must be put before the first recursive member definition. All CTE query definitionsare anchor members unless they reference the CTE itself.
• Anchor members must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT. UNION ALL is the only set operator allowed between the last anchor member and first recursive member, and when combining multiple recursive members.
• The number of columns in the anchor and recursive members must be the same.
• The data type of a column in the recursive member must be the same as the data type of the corresponding column in the anchor member.
• The FROM clause of a recursive member must refer only one time to the CTE expression_name.

• The following items are not allowed in the CTEquerydefinition of a recursive member:

  • SELECT DISTINCT
  • GROUP BY
  • HAVING
  • Scalar aggregation
  • TOP
  • LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed)
  • Subqueries
  • A hint applied to a recursive reference to a CTE inside a CTEquerydefinition.

To prevent an infinite loop in recursive CTE, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement.

Syntax In general form a recursive CTE has the following syntax:

WITH cte_alias (column_aliases)  
AS  
( 
cte_query_definition   --initialization 
UNION ALL 
cte_query_definition2 --recursive execution 
)  
SELECT * FROM cte_alias

Recursive Query Example

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE OPTION (MAXRECURSION 5)
GO

Notes

• CTE are not replacement of the Temp Table or Temp Variable Table
• Always begin CTE with semi-comma
• The scope of the CTE is limited to very first SELECT statement
• Usually the execution plan of the CTE like sub-query but there are cases when it can be different as well

Advantages of using CTE

• Create a recursive query, like hierarchy tree using Common Table Expressions.
• 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.
• Using CTE improves the readability and makes maintenance of complex queries easy.
• The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated.
• CTE can be defined in functions, stored procedures, triggers or even views.
• After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.
• If you are calling CTE twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don't have indexes/statistics (With the exception of any declared primary key on the table variable) there's no way of saying which will be faster.
• CTEs are very useful for on the fly IDENTITY columns:

How can you rewrite a subquery using CTE?
We can rewrite sub-queries using the Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL 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.

Rewrite a subquery using CTE Example 1

--Using Subquery
USE NORTHWIND
GO
SELECT C.CustomerID, C.CompanyName,
(SELECT COUNT(*) FROM Orders O WHERE O.CustomerID = C.CustomerID)
FROM   Customers C
GO

--Using Common Table Expression    
USE NORTHWIND
GO
WITH OrderCnt (CustomerID, Cnt) AS (
SELECT CustomerID, COUNT(*)
FROM   Orders
GROUP  BY CustomerID
)
SELECT C.CustomerID, C.CompanyName, coalesce(OC.Cnt, 0)
FROM   Customers C
LEFT   JOIN OrderCnt OC ON C.CustomerID = OC.CustomerID93

Rewrite a subquery using CTE Example 2

USE AdventureWorks
GO
--Using Subquery
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN (SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory) AS ecte 
ON ed.DepartmentID = ecte.DepartmentID
GO

--Using Common Table Expression
WITH EmployeeDepartment_CTE AS (
SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory
)
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.DepartmentID

http://www.simple-talk.com/sql/t-sql-programming/sql-server-2005-common-table-expressions

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

http://pushpendra-sql-ssis.blogspot.com/2011/02/common-table-expressionscte.html

http://www.sqllion.com/2010/08/common-table-expressions-cte

http://blog.sqlauthority.com/2011/07/16/sql-server-interview-questions-and-answers-frequently-asked-questions-day-16-of-31

What are CTEs inside SQL Server

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.

what are its advantages

     A CTE can be used to: 
     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.
     Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple, logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated. 
     CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.
     Create a recursive query, like hierarchy tree using Common Table Expressions. 
     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. 
     Using CTE improves the readability and makes maintenance of complex queries easy. 
     The query can be divided into separate, simple, logical building blocks which can be then used to build more complex CTEs until final result set is generated. 
     CTE can be defined in functions, stored procedures, triggers or even views. 
     After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data. 
     If you are calling CTE twice or more in the same function, you might get better performance if you fill a table variable and then join to/select from that. However, as table variables take up space somewhere, and don't have indexes/statistics (With the exception of any declared primary key on the table variable) there's no way of saying which will be faster. 
     CTEs are very useful for on the fly IDENTITY columns:

Structure of CTE

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

AS 

( CTE_query_definition ) 

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT  

FROM expression_name; 

how can you rewrite a subquery using CTE?

Co-related subquery Vs CTE

SELECT C.CustomerID, C.CompanyName,
(SELECT COUNT(*) FROM Orders O WHERE O.CustomerID = C.CustomerID)
FROM   Customers C


WITH OrderCnt (CustomerID, Cnt) AS (
SELECT CustomerID, COUNT(*)
FROM   Orders
GROUP  BY CustomerID
)
SELECT C.CustomerID, C.CompanyName, coalesce(OC.Cnt, 0)
FROM   Customers C
LEFT   JOIN OrderCnt OC ON C.CustomerID = OC.CustomerID

Sub Query Vs CTE

SELECT * FROM  (
        SELECT A.Address, E.Name, E.Age From Address A
        Inner join Employee E on E.EID = A.EID) T
WHERE T.Age > 50
ORDER BY T.NAME

With T1(Address, Name, Age)  --Column names for Temporary table
AS
(
   SELECT A.Address, E.Name, E.Age from Address A
   INNER JOIN EMP E ON E.EID = A.EID
),
T2(Name, Desig)
AS
(
  SELECT NAME, DESIG FROM Designation)
  SELECT T1.*, T2.Desig FROM T1  --SELECT or USE CTE temporary Table
WHERE T1.Age > 50 AND T1.Name = T2.Name
ORDER BY T1.NAME

how innovatively have you used CTEs?

1. The very best place I used CTE is for Server side pagination as below:

CREATE PROC GetPagedEmployees (@NumbersOnPage INT=25,@PageNumb INT = 1)
AS BEGIN

WITH AllEmployees AS
(SELECT ROW_NUMBER() OVER (Order by [Person].[Contact].[LastName]) AS RowID,
[FirstName],[MiddleName],[LastName],[EmailAddress] FROM [Person].[Contact])

SELECT [FirstName],[MiddleName],[LastName],[EmailAddress] 
FROM AllEmployees WHERE RowID BETWEEN 
((@PageNumb - 1) * @NumbersOnPage) + 1 AND @PageNumb * NumbersOnPage
ORDER BY RowID

END

1. To find out the tree level for data(Its pseudo code only, my apologies not giving the exact view of my system.)

DECLARE @t TABLE ( 
  id     INT, 
  parent INT) 

INSERT @t 
       (id, 
        parent) 
VALUES (1, 
        NULL), 
       (2, 
        1), 
       (3, 
        2), 
       (4, 
        3), 
       (5, 
        NULL), 
       (6, 
        5); 

WITH cte 
     AS (SELECT id, 
                parent, 
                CAST(RIGHT(Replicate('0', 12) + CONVERT(VARCHAR(12), id), 12) AS 
                     VARCHAR 
                     ( 
                     MAX)) 
                PATH 
         FROM   @t 
         WHERE  parent IS NULL 
         UNION ALL 
         SELECT child.id, 
                child.parent, 
                parent.PATH + RIGHT(Replicate('0', 12) + 
                              CONVERT(VARCHAR(12), child.id), 12) AS 
                PATH 
         FROM   @t child 
                JOIN cte parent 
                  ON parent.id = child.parent) 
SELECT * 
FROM   cte 
ORDER  BY PATH  

Can there be recursive CTEs? Yes.Here the query executes itself, like we use recursive functions (functions calling itself) in C, java or C# .net programming languages. This is very useful in case of parent-child hierarchy relationship or base-sub class or category-subcategory, etc. To prevent an infinite loop in recursive CTE, you can limit the number of recursion levels allowed for a particular statement by using the MAXRECURSION hint and a value between 0 and 32,767 in the OPTION clause of the INSERT, UPDATE, MERGE, DELETE, or SELECT statement.

USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE OPTION (MAXRECURSION 5)
GO

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps.

Structure of a Recursive CTE

The structure of the recursive CTE in Transact-SQL is similar to recursive routines in other programming languages. Although a recursive routine in other languages returns a scalar value, a recursive CTE can return multiple rows.

A recursive CTE consists of three elements:

• Invocation of the routine.

The first invocation of the recursive CTE consists of one or more CTEquerydefinitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.

CTEquerydefinitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

• Recursive invocation of the routine.

The recursive invocation includes one or more CTEquerydefinitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

• Termination check.

The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

Pseudocode and Semantics

The recursive CTE structure must contain at least one anchor member and one recursive member. The following pseudocode shows the components of a simple recursive CTE that contains a single anchor member and single recursive member.

WITH ctename ( columnname [,...n] )

AS

(

CTEquerydefinition –- Anchor member is defined.

UNION ALL

CTEquerydefinition –- Recursive member is defined referencing cte_name.

)

-- Statement using the CTE

SELECT *

FROM cte_name

The semantics of the recursive execution is as follows:

1.Split the CTE expression into anchor and recursive members.

2.Run the anchor member(s) creating the first invocation or base result set (T0).

3.Run the recursive member(s) with Ti as an input and Ti+1 as an output.

4.Repeat step 3 until an empty set is returned.

5.Return the result set. This is a UNION ALL of T0 to Tn.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);


USE AdventureWorks2008R2;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID, 
        0 AS Level
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    WHERE ManagerID IS NULL
    UNION ALL
-- Recursive member definition
    SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
        Level + 1
    FROM dbo.MyEmployees AS e
    INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
        ON e.EmployeeID = edh.BusinessEntityID AND edh.EndDate IS NULL
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, DeptID, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
    ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Sales and Marketing' OR Level = 0;
GO

Example Code Walkthrough

1.The recursive CTE, DirectReports, defines one anchor member and one recursive member.

2.The anchor member returns the base result set T0. This is the highest ranking employee in the company; that is, an employee who does not report to a manager.

Here is the result set returned by the anchor member:

ManagerID      EmployeeID           Title                         Level
NULL                1          Chief Executive Officer                0

3.The recursive member returns the direct subordinate(s) of the employee in the anchor member result set. This is achieved by a join operation between the Employee table and the DirectReports CTE. It is this reference to the CTE itself that establishes the recursive invocation. Based on the employee in the CTE DirectReports as input (Ti), the join (MyEmployees.ManagerID = DirectReports.EmployeeID) returns as output (Ti+1), the employees who have (Ti) as their manager. Therefore, the first iteration of the recursive member returns this result set:

ManagerID        EmployeeID       Title                         Level
1                   273        Vice President of Sales               1

4.The recursive member is activated repeatedly. The second iteration of the recursive member uses the single-row result set in step 3 (containing EmployeeID 273) as the input value, and returns this result set:

ManagerID    EmployeeID          Title                                Level
273                 16         Marketing Manager                         2
273                 274        North American Sales Manager              2
273                 285        Pacific Sales Manager                     2

The third iteration of the recursive member uses the result set above as the input value, and returns this result set:

ManagerID EmployeeID               Title                         Level
16                 23         Marketing Specialist                   3
274                275        Sales Representative                   3
274                276        Sales Representative                   3
285                286        Sales Representative                   3

5.The final result set returned by the running query is the union of all result sets generated by the anchor and recursive members.

Here is the complete result set returned by the example:

ManagerID    EmployeeID              Title                                Level
NULL              1          Chief Executive Officer                         0
1                 273        Vice President of Sales                         1
273               16         Marketing Manager                               2
273              274        North American Sales Manager                     2
273              285        Pacific Sales Manager                            2
16                 23         Marketing Specialist                           3
274              275        Sales Representative                             3
274              276        Sales Representative                             3
285             286        Sales Representative                              3

Advantages

As discussed above, the use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies

• CTEs make queries more simpler and easy to read.
• Can be used to create a recursive query.
• Allow grouping by a column which might be derived from a scalar subset
• Can reference itself multiple times