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