Tuesday, January 3, 2017

What is MAXRECURSION hint in Recursive CTE?

Common Table Expression (CTE) provides a mechanism to write easy to understand, more readable and maintainable recursive queries. We can reference them as a temporary result set which is defined within the execution scope of a single SQL statement. If you are working on recursive queries with CTE feature of SQL Server then you will get the following error if you are not using MAXRECURSION query hint at the end of the query–
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
If the MAXRECURSION query hint is not specified, the default limit is 100. When the specified or default number for MAXRECURSION limit is reached during query execution, the query is ended and an error is returned.

What is MAXRECURSION hint in Recursive CTE?
MAXRECURSION hint allows us to specify how often the CTE can recur before generating an error. MAXRECURSION hint is very helpful in a scenario where poorly written CTE is resulting in an infinite recursion level; in such cases MAXRECURSION terminates the CTE once the defined recursion crosses. MAXRECURSION 0 allows infinite recursion.
While creating VIEW do not use this clause but whenever you are executing the VIEW use it there instead.

Just understand MAXRECURSION hint
To understand the worth of MAXRECURSION hint, we are taking a recursive example for employee data which is just a sample to avoid max recursion error. In this query, we have to use hint (MAXRECURSION 200) or hint (MAXRECURSION 0) and see the behavior of the CTEs queries.
A)   Hint (MAXRECURSION 200)
In the below example, MAXRECURSION hint value is 200 which terminates the CTE execution once it reaches the recursion level of 200 and terminates the statement with the error –
;WITH CTE AS
(
 SELECT EMPID=101, EMPNAME='ABC_101'
 UNION ALL
 SELECT EMPID=EMPID+1, EMPNAME='ABC_'+CAST(EMPID+1 AS VARCHAR(3))
 FROM CTE
 WHERE EMPID<1000
)
SELECT * FROM CTE
OPTION (MAXRECURSION 200);

B)    Hint (MAXRECURSION 0)
If we have to  come across a scenario, where we need to have recursion level till the end of the recursive condition then we can achieve this by specifying MAXRECURSION value as 0 means no limit to the recursion level as given below- 
;WITH CTE AS
(
 SELECT EMPID=101, EMPNAME='ABC_101'
 UNION ALL
 SELECT EMPID=EMPID+1, EMPNAME='ABC_'+CAST(EMPID+1 AS VARCHAR(3))
 FROM CTE
 WHERE EMPID<1000
)
SELECT * FROM CTE
OPTION (MAXRECURSION 0);

Conclusion
The MAXRECURSION query hint specifies the maximum number of recursions allowed for a query. The number of recursions is a non-negative integer between 0 and 32,767. When 0 is specified, no limit is applied.

1 comment:

  1. Small business owners and entrepreneurs are good at working on their own and thinking outside the box. When things aren't working though, we are not good at something which, if left unchecked, can destroy our business - asking for help daceasy

    ReplyDelete

Popular Posts