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.