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.