Search This Blog

New Features - Common Table Expressions (CTEs)

 

 

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.

No comments: