Recursive CTE | SQL with Manoj

Recursive CTE error – The maximum recursion 100 has been exhausted before statement completion

December 23, 2011 12 comments

One of the most benefit of CTE (Common Table Expressions) is that we can create recursive queries with them. In my previous posts I’ve discussed this topic with some good examples, link.

An incorrectly composed recursive CTE may cause an infinite loop. So recursive CTEs should be designed very carefully and the recursion level should be checked. To prevent it to run infinitely SQL Server’s default recursion level is set to 100. But you can change the level by using the MAXRECURSION option/hint. The recursion level ranges from 0 and 32,767.

If your CTEs recursion level crosses the limit then following error is thrown by SQL Server engine:
Msg 530, Level 16, State 1, Line 11
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Let’s check this with an example discussed in MSDN TSQL forum, link:

DECLARE
	@startDate DATETIME,
	@endDate DATETIME

SET @startDate = '11/10/2011'
SET @endDate = '03/25/2012'

; WITH CTE AS (
	SELECT
		YEAR(@startDate) AS 'yr',
		MONTH(@startDate) AS 'mm',
		DATENAME(mm, @startDate) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		@startDate 'new_date'
	UNION ALL
	SELECT
		YEAR(new_date) AS 'yr',
		MONTH(new_date) AS 'mm',
		DATENAME(mm, new_date) AS 'mon',
		DATEPART(d,@startDate) AS 'dd',
		DATEADD(d,1,new_date) 'new_date'
	FROM CTE
	WHERE new_date < @endDate
	)
SELECT yr AS 'Year', mon AS 'Month', count(dd) AS 'Days'
FROM CTE
GROUP BY mon, yr, mm
ORDER BY yr, mm
OPTION (MAXRECURSION 1000)

Output:-

Year	Month		Days
2011	November	22
2011	December	31
2012	January		31
2012	February	29
2012	March		24

Here, by applying “OPTION (MAXRECURSION 1000)”, we can set the recursion level, so that it does not go infinite.

Note: Restriction of recursive CTE is – “A view that contains a recursive CTE cannot be used to update data”.

More info on: http://msdn.microsoft.com/en-us/library/ms175972.aspx

SQL Trivia – Reverse a string without using T-SQL REVERSE() function

August 18, 2011 9 comments

Everyone knows that the REVERSE() function (as its name suggests) reverses a string’s value, MSDN link.

This post’s title talks about going the other way, but why? If we already have this function then why to reinvent the wheel.

But at times an Interviewer may trick you and asks this question randomly: “How will you reverse a string without using REVERSE() function? No loop, should be a single query.” People who have actually worked on CTEs and know about recursive CTEs can guess the answer and create the query on-spot. Actually he wants to know if you have really worked on CTEs & recursive CTEs, or just learned the concept, or do not know about this at all.

Let’s check this by a simple example:

DECLARE @StringToReverse VARCHAR(55)
SET @StringToReverse = 'Reverse a string with out using REVERSE() function'

;WITH cte AS (
      SELECT @StringToReverse AS string, CAST('' AS VARCHAR(55)) AS revStr, LEN(@StringToReverse) AS ln
      UNION ALL
      SELECT SUBSTRING(string,0,ln) AS string, CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55)) AS revStr, ln-1 AS ln
      FROM cte
      WHERE ln >= 1)
SELECT @StringToReverse AS String, revStr
FROM cte
WHERE ln = 0

Output:-

Note: In the code above check line numbers 5 & 7 containing CAST function, “CAST(” AS VARCHAR(55)) AS revStr” & “CAST(revStr + SUBSTRING(string,ln,1) AS VARCHAR(55))”.

Applying CAST or CONVERT function is necessary here as datatype & size of both the columns in the anchor & recursive part should be same. Otherwise SQL server will throw the following error:

Msg 240, Level 16, State 1, Line 4
Types don’t match between the anchor and the recursive part in column “revStr” of recursive query “cte”.

>> Check & Subscribe my [YouTube videos] on SQL Server.

Categories: SQL Tips, SQL Trivia Tags: CTE, CTE recursion, Recursive CTE, recursive query

CTE Recursion | Sequence, Dates, Factorial, Fibonacci series

May 23, 2011 14 comments

CTE, Common Table Expressions

According to MS BOL CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

More info on CTE can be found here: http://msdn.microsoft.com/en-us/library/ms190766.aspx

Some examples of CTE Recursion or Recursive CTE can be found here:

-- Number Sequence: 1 to 10
;with num_seq as (
	select 1 as num
	union all
	select num+1
	from num_seq
	where num<100)
select num
from num_seq
where num < 10

-- Date Sequence: May 2011
;with dt_seq as (
	select cast('5/1/2011' as datetime) as dt, 1 as num
	union all
	select dt+1, num+1
	from dt_seq
	where num<31)
select dt
from dt_seq

-- Factorial
;with fact as (
	select 1 as fac, 1 as num
	union all
	select fac*(num+1), num+1
	from fact
	where num<12)
select fac
from fact
where num=5

-- Fibonacci Series
;with fibo as (
	select 0 as fibA, 0 as fibB, 1 as seed, 1 as  num
	union all
	select seed+fibA, fibA+fibB, fibA, num+1
	from fibo
	where num<12)
select fibA
from fibo

More on Recursive CTE: http://msdn.microsoft.com/en-us/library/ms186243.aspx

Categories: SQL Tips Tags: CTE, CTE recursion, Recursive CTE