How to use CTE in SQL Server 2005 15

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.

 

15 thoughts on “How to use CTE in SQL Server 2005

  1. Pingback: New jordan

  2. Reply leo Mar 2, 2013 1:57 am

    cool article

  3. Reply R4 Mar 24, 2013 12:44 am

    this is great blog, I will certainly be back.

  4. Reply kwiaciarnia Mar 31, 2013 5:08 pm

    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.

  5. Reply panele operatorskie Apr 2, 2013 5:06 pm

    Excellent article!

  6. Reply prawo spadkowe Apr 7, 2013 7:26 pm

    Studying by way of your nice content material, will help me to do so sometimes.

  7. Reply Lenny Wilen Apr 13, 2013 10:36 pm

    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.

  8. Reply Milly Ketler Apr 20, 2013 12:00 pm

    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.

  9. Reply Hanh Scharte Apr 20, 2013 12:01 pm

    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.

  10. Reply Rosalee Zaugg Apr 20, 2013 12:02 pm

    Studying this write-up – the present of one’s time

  11. Reply Harold Bojanowski Apr 21, 2013 6:03 am

    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.

  12. Reply Darrick Roccia Apr 21, 2013 6:06 am

    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!

  13. Reply Nigel Swaggert Apr 21, 2013 6:07 am

    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!

  14. Reply Stanford Oriordan Apr 22, 2013 3:45 pm

    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.

  15. Reply kosiarka spalinowa Apr 29, 2013 12:17 pm

    Oh my goodness! an incredible article dude.

Leave a Reply

  

  

  


1 + 7 =