Understanding Recursive queries


Howdy Everyone…

With the introduction of common table expressions (aka CTE), people started using it for building recursive and hierarchical solutions… At a first glance at the solution I am not able to understand what is happening there…. So here is the comeplete explanation of how it is happening for an application programmer than a database expert.

Let the scenario be printing all the numbers from 100 to 1, How we might have accomplished this task without the use of CTE is as below

declare @n int
set @n = 100
while(@n > 0)
BEGIN
	SELECT @n
	SET @n = @n-1
END

Simple one, I think there is no need of further explanation from my end to explain the above stuff….
Now coming to the recursive queries…. how we will do the same…

declare @n table ( a int)
insert into @n values (100)


;with CTE
AS
(
	SELECT a from @n        --- Anchor Query
	UNION ALL
	SELECT (a-1) from cte   --- Recursive Query
	where (a-1) > 0
)
select * from cte

What is happening over there… too much code…. You might be thinking using the earlier method is easier… but believe me using the recursive queries might help you to avoid a large of number cursors in your implementation.
Now going into the explanation bit,
We have defined a table variable named @n which will hold the values of 100 in a column a.
In a recursive CTE, you should always have a anchor query followed by union all or else you will get the error from SQL (Recursive common table expression ‘CTE’ does not contain a top-level UNION ALL operator.)
Next we have to build the recursion by querying on the common table expression itself (in this case CTE), but you have to use limit where the recursion should end, in this case the where clause of the second select statement will do that (where (a-1) > 0)
Incase if you didn’t mention the where clause you will get an error from SQL Server with the max recurssion exceeded. The reason is SQL Server dont know when to stop the recursion or where to stop the recursion.
So, by default it will loop for 100 times and if it is more than that we get and error like (The statement terminated. The maximum recursion 100 has been exhausted before statement completion.)
We can restrict the same by using the query hint OPTION (MAXRECURSION <number>), which will limit the number of loops.

Advertisements

One thought on “Understanding Recursive queries

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s