This is one of the most exciting features introduced in SQL Server 2005. A CTE is nothing but a “temporary 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. In many cases, CTEs can be used to replace derived tables, temporary tables and table variables.
Syntax
WITH CTEName [ ( ColumnName[1..n] ) ]
AS
( CTE_QueryDefiniton )
SELECT <ColumnList>
FROM CTEName
The QueryDefinition contains the Select Query which populates the CTE
Example1
WITH MyFirstCTE ( Col)
AS
(SELECT 'Hello World' )
SELECT * FROM MyFirstCTE
Now in the above example, if you put one more select statement after the existing one, you will get the following error
WITH MyFirstCTE ( Col)
AS
(SELECT 'Hello World' )
SELECT * FROM MyFirstCTE
SELECT * FROM MyFirstCTE
Msg 208, Level 16, State 1, Line 6
Invalid object name 'MyFirstCTE'.
Example2
The following Query uses CTE to display the number of employees comes from a each city
USE [AdventureWorks]; -- ';' is needed here
WITH CTE_Address(City, total)
AS
( SELECT City, COUNT(*)
FROM Person.Address
GROUP BY City
)
SELECT City, total
FROM CTE_Address
ORDER BY Total
Example3
Find all the Managers using CTE
USE AdventureWorks ;
WITH CTE_EmpList ( EmployeeID, FirstName, LastName, Designation)
AS ( SELECT e.EmployeeID,
c.FirstName,
c.LastName,
e.Title
FROM HumanResources.Employee e
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE ManagerID IS NULL
)
SELECT *
FROM CTE_EmpList
Example4
Here comes the actual usage of CTEs. Suppose we need to list out all the employees reporting to a manager. In sql server 2000, you need to write a lot of stuffs to acquire this, but here this is very simple
USE AdventureWorks ;
WITH CTE_EmpList ( ManagerID, EmployeeID, Title, FirstName, LastName, EmployeeLevel )
AS (
-- anchor members
SELECT e.ManagerID,
e.EmployeeID,
e.Title,
c.FirstName,
c.LastName,
0 AS EmployeeLevel
FROM HumanResources.Employee e
INNER JOIN Person.Contact c ON e.ContactID = c.ContactID
WHERE ManagerID IS NULL
UNION ALL
-- recursive members
SELECT e.ManagerID,
e.EmployeeID,
e.Title,
c.FirstName,
c.LastName,
EmployeeLevel + 1
FROM HumanResources.Employee e
INNER JOIN CTE_EmpList d ON e.ManagerID = d.EmployeeID
JOIN Person.Contact AS c ON e.ContactID = c.ContactID )
SELECT *
FROM CTE_EmpList
ORDER BY employeeLevel
As you can see from the above, the code consists of 2 parts, anchor member part and the recursive members part. The anchor member part just selects the managers or the Root level, while the other selects all the employees reporting to these manages in a recursive manner, so that the UNION ALL of these will yield the desired output.
The key word MAXRECURSION can be used as a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. To use it in our above example, include it on the last line;
SELECT *
FROM CTE_EmpList
OPTION (MAXRECURSION 3)
The above Query will return the employee levels upto 3. Also, if the value specified in the Maxrecursion part is less that the maximum recursion level, then you may also get the following error message, which specifies that the Query has terminated beore it completes.
Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 3 has been exhausted before statement completion.
Here is the final example
WITH MyCTE ( x ,level)
AS (
SELECT x= CONVERT(VARCHAR(8000) , 0), 0 AS level
UNION ALL
SELECT x +' -> ' +CAST(LEVEL+1 AS VARCHAR), LEVEL+1
FROM MyCTE
WHERE LEN(x) < 50
)
SELECT x
FROM MyCTE
Notes
- WITH’ clause requires that the statement preceding it in the Batch must be terminated by a ‘;’, unless it is the first statement of the batch.
