Common table expression acts as temporary result set for the query (select, insert , delete and update or merge).
It can be use as derived table for storing result set and while joining with different tables.
CTE doesnot act as object and scope is limited to till query execution.
Can be use as alternative option of temporary table.
Two types of CTE possible in SQL Server – Recursive and Non Recursive.
How to use Non recursive Common table expression?
In nonrecursive common table expression doesnt refer itself within the CTE.It acts as simple temporary result set for the query and simple to implement CTE.
we will see nonrecursive CTE example to understand it better.
Example 1 : We are using select query to capture the result set in named CTE “empCTE” .
USE test
GO
WITH empCTE(empID, empname) AS
(
SELECT emp_id,emp_name FROM emp_details(NOLOCK)
)
SELECT * FROM empCTE
Example 2 : In this example we are using joins with CTE to extract matching records from CTE result set.
WITH empCTE AS
(
SELECT emp_id,emp_name FROM emp_details(NOLOCK)
)
SELECT * FROM emp_details(NOLOCK) a INNER JOIN empCTE b
ON a.emp_id = b.emp_id
Example 3: We are multiple CTE combined together
WITH empCTE AS
(
SELECT emp_id,emp_name FROM emp_details(NOLOCK)
),
empCTE1 as
(
select * from emp_details where emp_deg like ‘%DBA%’
)
SELECT * FROM empCTE1 a INNER JOIN empCTE b
ON a.emp_id = b.emp_id
Recursive CTE
Recursive CTE refers itself within CTE query. We can apply joins on recursive CTE within same CTE query by using UNION ALL, UNION, INTERSECT or EXPECT operators. Will go through the below example to understand what things should be remember before creating recursive CTE.
Now we are creating RecusiveCTE with single select query and applying inner join on emp_details and RecursiveCTE.
WITH RecursiveCTE AS
(
SELECT a.emp_id,a.emp_name,a.emp_address,b.emp_deg FROM emp_details(NOLOCK) a
INNER JOIN RecursiveCTE b ON a.emp_id = b.emp_id
)
SELECT * from RecursiveCTE
After executing the above CTE query we got below error
Msg 246, Level 16, State 1, Line 2
No anchor member was specified for recursive query “RecursiveCTE”.
As error suggest, we don’t have anchor member in our recursive query. First thing comes in the mind what is anchor member so answer is anchor member is nothing but the first statement in CTE query without any reference of same CTE. Second query acts as recursive member of the CTE query.
WITH RecursiveCTE AS
(
SELECT emp_id,emp_name,emp_address,emp_deg FROM emp_details(NOLOCK)
union All
SELECT a.emp_id,a.emp_name,a.emp_address,b.emp_deg FROM emp_details(NOLOCK) a
INNER JOIN RecursiveCTE b ON a.emp_id = b.emp_id
)
SELECT * from RecursiveCTE
When we executed above CTE query, we got below error message.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
If CTE query is not properly written it will go infinite loop and terminate with above error message. We can avoid this by using MAXRECURSION option while retriveing data from CTE in last select statement.
SELECT * from RecursiveCTE OPTION (MAXRECURSION 1)
After adding option it will allow to execute maximum 1 level of recursion.
Msg 252, Level 16, State 1, Line 4
Recursive common table expression ‘RecursiveCTE’ does not contain a top-level UNION ALL operator.
As per error if we are using multiple queries in CTE query that should combine with UNION ALL operator.
GROUP BY, HAVING, or aggregate functions are not allowed in the recursive common table expression ‘RecursiveCTE’.
WITH RecursiveCTE (job_id,job_desc,min_lvl,max_lvl,counter)AS
(
SELECT job_id,job_desc,min_lvl,max_lvl,1
FROM jobs
UNION ALL
SELECT a.job_id,a.job_desc,a.min_lvl,a.max_lvl,b.counter +1 FROM jobs a
inner JOIN RecursiveCTE b
ON a.min_lvl = b.max_lvl
)
SELECT * from RecursiveCTE
Written new query for our recursive CTE example and got the expected result.
Pingback: New jordan
cool article
this is great blog, I will certainly be back.
Wow! This can be one particular of the most useful blogs We have ever arrive across on this subject. Actually Fantastic. I am also a specialist in this topic so I can understand your effort.
Excellent article!
Studying by way of your nice content material, will help me to do so sometimes.
I am no longer positive where you are getting your info, but great topic. I must spend some time studying more or working out more. Thanks for excellent information I used to be on the lookout for this information for my mission.
Thanks for the sensible critique. Me and my neighbor were just preparing to do a little research on this. We got a grab a book from our local library but I think I learned more clear from this post. I’m very glad to see such excellent information being shared freely out there.
Thanks for another informative website. Where else could I get that kind of info written in such a perfect way? I have a project that I am just now working on, and I have been on the look out for such information.
Studying this write-up – the present of one’s time
I have read some good stuff here. Definitely worth bookmarking for revisiting. I wonder how much effort you put to make such a excellent informative website.
Pretty great post. I simply stumbled upon your weblog and wanted to mention that I’ve really enjoyed browsing your weblog posts. After all I will be subscribing to your feed and I’m hoping you write again very soon!
Good web site! I really love how it is simple on my eyes and the data are well written. I’m wondering how I might be notified whenever a new post has been made. I’ve subscribed to your RSS feed which must do the trick! Have a great day!
I’m so happy to read this. This is the kind of manual that needs to be given and not the random misinformation that is at the other blogs. Appreciate your sharing this greatest doc.
Oh my goodness! an incredible article dude.