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