Common Table Expressions (CTE)

CTEs come handy when dealing with the recursive functions

The expressions before CTE must end with a semicolon(;).

Tables on the remote server can be referenced by a CTE.

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.

Views can contain a CTE.

CTEs Should not use:
COMPUTE
INTO
ORDER BY
OPTION
FOR XML
FOR BROWSE

Recursive CTEs contain two parts:
Anchor member and Recursive member.

The number of columns in a recursive member must be exactly the same to that of the anchor member.

The column datatypes of the recursive member must be the same as the corresponding column in the anchor member.

The FROM clause of a recursive member must refer the CTE only one time

The following should not be used in a recursive CTE
SELECT DISTINCT
TOP
LEFT, RIGHT, OUTER joins (only INNER JOIN allowed) in a CTE
GROUP BY & HAVING
Scalar aggregation
Subqueries

Let us create a recursive CTE with an Employee table

CREATE TABLE EmployeesForCTE(EmpId INT PRIMARY KEY
, EmpName VARCHAR(100)
, MngrId INT CONSTRAINT FK_EmployeesForCTE FOREIGN KEY REFERENCES EmployeesForCTE(EmpId)
)
GO

INSERT INTO EmployeesForCTE
SELECT 1, 'Emp1', NULL UNION ALL
SELECT 2, 'Emp2', 1 UNION ALL
SELECT 3, 'Emp3', 1 UNION ALL
SELECT 4, 'Emp4', 2 UNION ALL
SELECT 5, 'Emp5', 3 UNION ALL
SELECT 6, 'Emp6', 5 UNION ALL
SELECT 7, 'Emp7', 4 UNION ALL
SELECT 8, 'Emp8', 2 UNION ALL
SELECT 9, 'Emp9', 8
GO

CTE Syntax:
WITH CTEName(Column1, Column2...Columnn)
AS
(
Anchor member
UNION ALL
Recursive member
)

WITH EmpHierarchy(EmpId, Empname, MngrId, MngrName)
AS
( SELECT EmpId, Empname, MngrId, '' AS MngrName
FROM EmployeesForCTE WHERE MngrId IS NULL -- Anchor member
UNION ALL
SELECT Emp.EmpId, Emp.EmpName, Mngr.EmpId AS MngrId, Mngr.EmpName AS MngrName -- Recursive member
FROM EmployeesForCTE AS Emp
INNER JOIN EmpHierarchy AS Mngr ON Mngr.EmpId = Emp.MngrId
)

SELECT * FROM EmpHierarchy

I have given '' as the MngrName indicating it as a varchar field

Run the above query and you will get
Msg 240, Level 16, State 1, Line 1
Types don''t match between the anchor and the recursive part in column "MngrName" of recursive query "EmpHierarchy".

Now I use a cast function and make Mngrname as varchar

WITH EmpHierarchy(EmpId, Empname, MngrId, MngrName)
AS
( SELECT EmpId, Empname, MngrId, CAST('' AS VARCHAR(1000)) AS MngrName
FROM EmployeesForCTE WHERE MngrId IS NULL -- Anchor member
UNION ALL
SELECT Emp.EmpId, Emp.EmpName, Mngr.EmpId AS MngrId, Mngr.EmpName AS MngrName -- Recursive member
FROM EmployeesForCTE AS Emp
INNER JOIN EmpHierarchy AS Mngr ON Mngr.EmpId = Emp.MngrId
)

SELECT * FROM EmpHierarchy


Run the above query and you will still get
Msg 240, Level 16, State 1, Line 1
Types don''t match between the anchor and the recursive part in column "MngrName" of recursive query "EmpHierarchy".

Now, give VARCHAR(100) as the size of MngrName as it is for the EmpName in the EmployeesForCTE table and try

WITH EmpHierarchy(EmpId, Empname, MngrId, MngrName)
AS
( SELECT EmpId, Empname, MngrId, CAST('' AS VARCHAR(100)) AS MngrName
FROM EmployeesForCTE WHERE MngrId IS NULL -- Anchor member
UNION ALL
SELECT Emp.EmpId, Emp.EmpName, Mngr.EmpId AS MngrId, Mngr.EmpName AS MngrName -- Recursive member
FROM EmployeesForCTE AS Emp
INNER JOIN EmpHierarchy AS Mngr ON Mngr.EmpId = Emp.MngrId
)

SELECT * FROM EmpHierarchy

It works. What does it say?
The datatypes of the corresponding columns in anchor and recusive members should be perfect match in type and size.

Now, try

Now, give VARCHAR(100) as the size of MngrName as it is for the EmpName in the EmployeesForCTE table and try

WITH EmpHierarchy(EmpId, Empname, MngrId, MngrName)
AS
( SELECT EmpId, Empname, MngrId, CAST('' AS VARCHAR(100)) AS MngrName
FROM EmployeesForCTE WHERE MngrId IS NULL -- Anchor member
UNION ALL
SELECT Emp.EmpId, Emp.EmpName, Mngr.EmpId AS MngrId, Mngr.EmpName AS MngrName -- Recursive member
FROM EmployeesForCTE AS Emp
INNER JOIN EmpHierarchy AS Mngr ON Mngr.EmpId = Emp.MngrId
)
SELECT TOP 1 1 FROM EmpHierarchy
SELECT * FROM EmpHierarchy

What do you get:
A result set with one row and an error message


(1 row(s) affected)
Msg 208, Level 16, State 1, Line 12
Invalid object name 'EmpHierarchy'.

A CTE should be used immediately and that too only once.
So place the results in a temp table or a table variable to use them when required at a later point of time.


WITH EmpHierarchy(EmpId, Empname, MngrId, MngrName)
AS
( SELECT EmpId, Empname, MngrId, CAST('' AS VARCHAR(100)) AS MngrName
FROM EmployeesForCTE WHERE MngrId IS NULL -- Anchor member
UNION ALL
SELECT Emp.EmpId, Emp.EmpName, Mngr.EmpId AS MngrId, Mngr.EmpName AS MngrName -- Recursive member
FROM EmployeesForCTE AS Emp
INNER JOIN EmpHierarchy AS Mngr ON Mngr.EmpId = Emp.MngrId
)

SELECT * INTO #EmpHier FROM EmpHierarchy
SELECT 1 AS Test
SELECT * FROM #EmpHier
DROP TABLE #EmpHier

DROP TABLE EmployeesForCTE
GO
I did not go much into CTE optimization yet, but this can be an easy start for a CTE.