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.

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

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.

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

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.

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:

initial anchor value

first recursed value

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?

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:

Select * from Org

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

Share

About the Author

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

Is it possible to refer CTE twice?
i.e can we use twice in different query?
For Example
WITH temp_table (column1, column2,...)
as
(
.... query ...
)
Select column1 from temp_table
select max(column1) from temp_table

Sorry, I did the second select using the partition command. Internal order of the heap table isn't impacted by the delete. You still can't do that with a group by statement or with a subquery using the partition command.

Fix your "Sara" example, there isn't a field called RID. That could be handled with a group by and join, but here is a data sample that couldn't be handled that way:
DECLARE @EMP table (EID int, ENAME varchar(20), DEPT varchar(10))
INSERT INTO @emp
values(1,'Sara','IT')
,(2,'Rick','HR')
,(3,'Ted','IT')
,(4,'Sheldon','Accounts')
,(5,'Sara','IT')
,(1,'Sara','IT')

selecting @emp before and after the cte's delete would demonstrate the effect of that command on a heap table as well.

The tie-in with the cte to the table is important information that I didn't realize existed until now. That tid bit is worth a 5 alone.