Click here to Skip to main content
15,868,005 members
Articles / Database Development / SQL Server

CTE In SQL Server

Rate me:
Please Sign up or sign in to vote.
4.87/5 (38 votes)
1 Nov 2011CPOL5 min read 231.1K   40   10
CTE In SQL Server

Introduction

For any operation over a temporary result set, what are the options SEL Server has to offer? We do have a Temp table, a table variable, table valued parameters and of course not to forget table valued function. But with the onset of SQL Server 2005 and onwards, a very powerful feather has been added for the programmers' benefit: Common Table Expression (CTE). It simplifies complex queries and most importantly enables you to recurse, did I just say recurse values. Yes, similar to any programming languages C#, C++, etc. Amazing, isn’t it ! Let's dive deep into this powerful feature and understand how it works and what all it has to offer.

CTE is again a temporary result set derived from the underling definition. For syntax of CTE, please refer to MSDN.

A Simple Illustration: CTE as a Derived Table

We have a simple table Products in our database.

Select * from PRODUCTS
ProductID ProductDesc ManufacturingDate       ExpiryDate              IsSalable Price
---------------------------------------------------------------------------------------
1	  Biscuits    2011-09-01 00:00:00.000  2012-09-01 00:00:00.000 1	20.00
2	  Butter      2010-09-01 00:00:00.000  2011-09-01 00:00:00.000 1	30.00
3	  Milk	     2011-10-01 00:00:00.000 2011-11-01 00:00:00.000  1	46.00

We have created a simple ProductsCTE for displaying all the Products with Price greater than 20.00. Here CTE performs the job of acting as a simple derived table.

SQL
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT * FROM ProductsCTE
ProdName                                           Price
-------------------------------------------------- ---------------------
Butter                                             30.00
Milk                                               46.00

(2 row(s) affected)

Important point that needs a mention is the SELECT followed by the CTE definition, any operation SELECT, INSERT, UPDATE, DELETE or Merge can be performed immediately after the CTE and the CTE lasts only for a single such operation. When I say that, what do I mean...

It means that the below code is erroneous:

SQL
DECLARE @T INT,@I INT
SET @T = 10
SET @I = 20
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT @T+@I
SELECT * FROM ProductsCTE

On executing the code, it results in the below error. Which means I need to execute the ProductsCTE SELECT immediately after the CTE definition completes.

Msg 422, Level 16, State 4, Line 10
Common table expression defined but not used.

To rectify the same... I would reinstate the order as:

SQL
DECLARE @T INT,@I INT
SET @T = 10
SET @I = 20
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT * FROM ProductsCTE
SELECT @T+@I
ProdName                                           Price
-------------------------------------------------- ---------------------
Butter                                             30.00
Milk                                               46.00

(2 row(s) affected)

-----------
30

(1 row(s) affected)

Let’s perform an UPDATE:

SQL
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
UPDATE ProductsCTE SET Price=50 WHERE ProdName='Milk'
SELECT * FROM ProductsCTE
-------------------

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

The price for Milk gets duly updated to 50 but the next set of select doesn’t work. Always remember, you can hit for a result set once and hit immediately after the CTE definition. Like:

SQL
;WITH ProductsCTE(ProdName,Price) AS
( SELECT ProductDesc,Price
  FROM PRODUCTS
  WHERE Price>20.00
)
SELECT * FROM ProductsCTE
UNION
SELECT 'Bread' AS ProdName,MIN(Price) AS PRICE from ProductsCTE

ProdName                                           Price
-------------------------------------------------- ---------------------
Bread                                              30.00
Butter                                             30.00
Milk                                               50.00

(3 row(s) affected)
Calling Multiple CTEs

We can have multiple CTEs calls from one single query. Let’s have a look at the example. We have 2 tables:

SQL
Select * from Student

Image 1

SQL
Select * from Teacher

Image 2

Let’s implement a CTE to get all the respective class teachers for the students.

SQL
;WITH StudCTE(RollNo,StudentName,TeacherID)
AS
(
SELECT ID,Name,TID FROM Student
)
,TeacherCTE(TID,TeacherName)
AS
(
SELECT ID,Name FROM Teacher
)
SELECT RollNo,StudentName,TeacherName
FROM StudCTE SC
INNER JOIN
TeacherCTE TC
ON SC.TeacherID=TC.TID

Image 3

We have called 2 CTEs from a single SELECT and based upon the inner join returned the student-teacher information. That was a simple example to show how multiple CTEs are done.

Complex Scenarios

So what is the big deal about CTE, the deal is when you need some complex queries or operations, trust me nothing goes as good as CTE. Let’s have a look at one of the most commonly encountered complex issues... Duplicates. We have a sample table (EMP) for the example.

SQL
Select * from EMP

EID         ENAME                DEPT
----------- -------------------- ----------
1           Sara                 IT
2           Rick                 HR
3           Ted                  IT
4           Sheldon              Accounts
5           Sara                 IT

(5 row(s) affected)

For removing the duplicate employee, i.e. ‘Sara’ from the table, we create a CTE:

SQL
;WITH EliminateDup(Eid,Name,Dept,RowID) AS
(
SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID
FROM EMP
)
DELETE FROM EliminateDup WHERE RID>1
The query below creates a temporary result set as :
SELECT Eid,Ename,Dept, ROW_NUMBER()OVER(PARTITION BY Ename,Dept ORDER BY EID)AS RowID
FROM EMP

Eid         Ename                Dept       RowID
----------- -------------------- ---------- --------------------
2           Rick                 HR         1
1           Sara                 IT         1
5           Sara                 IT         2
4           Sheldon              Accounts   1
3           Ted                  IT         1

(5 row(s) affected)

And later, we remove the duplicate with the DELETE. Quite simple, isn’t it.

Recursion

The next and the most important feature is recursion.

With the UNION ALL, we can make the CTE recursive to formulate a final result. There is an anchor member and a recursive member which may or may not have a terminating condition. Let’s see with an example..

Suppose we have a comma separated string and we wish to extract each word from the string..

Let’s consider the string to be ‘Where,there,is,a,will,there,is,a,way’.

SQL
DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(

SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select SUBSTRING(@T,Start,[End]-Start)from MyCTE;

Image 4

Let’s understand what we have done here... We have an anchor in the form of SELECT 1,CHARINDEX(',',@T,1).

For the first pass, the anchor returns the values 1,6 (this value being the CHARINDEX of first comma after the word ‘Where,’) for columns Start & [End].

Next the recursive code returns [End]+1=7 as Start and 12 for CHARINDEX(',',@T,[End]+1, i.e. 7) AS [End], this code recurses unless the terminating condition is met which is [End]<LEN(@T)i.e. 37.

The UNION ALL operator unites all the start & [End], for clarity let's take another look at the values.

SQL
DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(

SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE;

Image 5

Hope that makes things clearer. With CTE, we can achieve the same feats of programmability as C# or C++ with respect to generating Fibonacci series, a specific string patterns, etc. The recursion specifically finds an important use while you need a hierarchy to be reported, we will see that in a while. Currently, let’s look into the recursion option.

What if we want the first two values only out of the string?

SQL
DECLARE @T VARCHAR(100)='Where,there,is,a,will,there,is,a,way'
SET @T =@T+','
;WITH MyCTE(Start,[End]) AS(

SELECT 1 AS Start,CHARINDEX(',',@T,1) AS [End]
UNION ALL
SELECT [End]+1 AS Start,CHARINDEX(',',@T,[End]+1)AS [End] from MyCTE where [End]<LEN(@T)
)
Select Start,[End],SUBSTRING(@T,Start,[End]-Start)AS String from MyCTE
OPTION (MAXRECURSION 1);

The OPTION MAXRECURSION enables the code to recurse only once and terminates as soon as that happens.The self explanatory message flashes and the values returned out on the results pane is:

  1. initial anchor value
  2. first recursed value

Image 6

Image 7

MAXRECURSION value can be between 0 and 32,767. 32,767 is fine but would what a 0 return? 0 enables an infinite recursion hence if the recursive statement does not have a terminating condition, the program loops infinitely. For first hand experience, try the below code?

SQL
;WITH MyCTE(Val) AS(

SELECT 1 AS Val
UNION ALL
SELECT Val=(Val+1) FROM MyCTE
)
Select Val from MyCTE
OPTION (MAXRECURSION 0);

Fetching Hierarchy

Before we call it a day, let’s look at the final example of fetching the complete hierarchy of a particular organization. In such scenarios, CTE could outperform any complex code both in terms of simplicity and LOC (lines of code) required to derive the result.

We have a table Org as below:

SQL
Select * from Org

Image 8

For fetching the bottom up hierarchy, we pass the eid and get the complete hierarchy for the concerned employee. For example, for Andy’s organizational hierarchy, we pass @T = 6 (His Eid).

SQL
DECLARE @T INT = 6
;WITH OrgCTE(Eid,Employee,SupervisorID,ReportsTo)AS
(
SELECT @T,O.Name,O2.EID,O2.Name FROM Org O
INNER JOIN Org O2 ON O.SupervisorID=O2.EID
AND O.EID=@T

UNION ALL

SELECT OC.SupervisorID,OC.ReportsTo,O2.EID,O2.Name
FROM OrgCTE OC
INNER JOIN
Org O
ON OC.SupervisorID=O.EID
INNER JOIN
Org O2
ON O.SupervisorID=O2.EID
)
SELECT * FROM OrgCTE

Image 9

So we have been able to get the hierarchy for Andy.

Similarly for the top down hierarchy, we can implement the below CTE which gives the level indicating the top down org chart.

SQL
;WITH OrgCTE(Eid,SupervisorID,Employee,[Role],[Level])AS
(
SELECT EID,SupervisorID,Name,[Role],0 FROM
Org WHERE SupervisorID=0

UNION ALL

SELECT O.EID,O.SupervisorID,O.Name,O.[Role],[Level]+1
FROM Org O
INNER JOIN OrgCTE OC
ON O.SupervisorID=OC.Eid
)
SELECT * FROM OrgCTE

Image 10

SQL programmers find CTE of immense use and thanks to this feature, complexities in programming life have been considerably simplified. I hope I have been able to justify CTE reasonably well in this article.

History

  • 31st October, 2011: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer
India India
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!

Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".

Comments and Discussions

 
PraiseThanks and it's informative. Pin
GovindharajK3-Apr-17 7:22
GovindharajK3-Apr-17 7:22 
Smile | :)
Questionvery nice article Pin
Suryakumar00728-Jun-15 20:55
Suryakumar00728-Jun-15 20:55 
QuestionCan CTE referenced twice Pin
Rajanand Ilangovan18-Jul-13 5:20
Rajanand Ilangovan18-Jul-13 5:20 
AnswerRe: Can CTE referenced twice Pin
Dineshshp6-Dec-13 22:10
professionalDineshshp6-Dec-13 22:10 
GeneralRe: Can CTE referenced twice Pin
Rajanand Ilangovan27-Jan-14 6:29
Rajanand Ilangovan27-Jan-14 6:29 
GeneralRe: Can CTE referenced twice Pin
Maciej Los11-May-14 7:10
mveMaciej Los11-May-14 7:10 
GeneralRe: Can CTE referenced twice Pin
Maciej Los11-May-14 7:10
mveMaciej Los11-May-14 7:10 
QuestionScratch that. Pin
KP Lee7-Nov-11 19:57
KP Lee7-Nov-11 19:57 
QuestionCTEs gone wrong Pin
KP Lee7-Nov-11 19:43
KP Lee7-Nov-11 19:43 
AnswerRe: CTEs gone wrong Pin
Keshav Singh7-Nov-11 20:55
Keshav Singh7-Nov-11 20:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.