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.
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.
No comments:
Post a Comment