{"id":3948,"date":"2015-09-05T00:59:13","date_gmt":"2015-09-04T19:29:13","guid":{"rendered":"http:\/\/sqlhints.com\/?p=3948"},"modified":"2015-09-05T08:49:03","modified_gmt":"2015-09-05T03:19:03","slug":"recursive-cte-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2015\/09\/05\/recursive-cte-sql-server\/","title":{"rendered":"Recursive CTE – Sql Server"},"content":{"rendered":"

This is the second article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:<\/p>\n

\nIntroduction to Common Table Expression (a.k.a CTE)<\/a>
\nMultiple CTEs in a Single Query<\/a>
\nNested Common Table Expressions<\/a>\n<\/p>\n

Recursive Common Table Expression<\/h3>\n

CTEs provide a mechanism to write easy to understand, more readable and maintainable recursive queries<\/strong>. Prior to CTEs only mechanism to write recursive query is by means of recursive function or stored procedure. And these recursive functions or stored procedures support only up-to 32 levels of recursion. By default CTEs support a maximum recursion level of 100. CTEs also provide an option to set a MAXRECURSION level value between 0 to 32,767. Specifying it’s value as 0 means no limit to the recursion level, you agreed for a risk in case of a poorly written query resulting in infinite recursion level.<\/p>\n

Recursive CTE Example:<\/h3>\n

Below is a very basic example of a recursive CTE generating numbers between 1 to 10:<\/p>\n

\"RecursiveCTE<\/a><\/p>\n

WITH NumbersCTE AS\r\n(\r\n\tSELECT  1 AS Number\r\n\tUNION ALL\r\n\tSELECT Number + 1 FROM NumbersCTE\r\n\tWHERE Number < 10\r\n )\r\nSELECT * FROM NumbersCTE<\/pre>\n
RESULT:<\/strong>
\n\"Recursive<\/a><\/p>\n
Example of Recursive CTE to get Managers and employees reporting to them<\/h3>\n
This example uses the Employees<\/strong> table created in the previous article Introduction to Common Table Expression (a.k.a CTE)<\/a>. You can visit the link to create the table, if you have not created it already.<\/p>\n
\r\nWITH EmpRecursiveCTE(ManagerId, EmployeeId, Name, Level) AS \r\n(\r\n    SELECT ManagerId, Id, Name, 0 AS Level\r\n    FROM dbo.Employees \r\n    WHERE ManagerId IS NULL\r\n\t\tUNION ALL\r\n    SELECT E.ManagerId, E.Id, E.Name, Level + 1\r\n    FROM dbo.Employees E\r\n        INNER JOIN EmpRecursiveCTE EmpCTE\r\n        ON E.ManagerID = EmpCTE.EmployeeId \r\n)\r\nSELECT EmployeeId, Name, ManagerID, Level \r\nFROM EmpRecursiveCTE\r\nORDER BY ManagerID\r\n<\/pre>\n
RESULT:<\/strong>
\n\"Recursive<\/a><\/p>\n
MAXRECURSION hint in Recursive CTE<\/h3>\n
By default maximum recursion level supported by CTE is 100. But CTE provides an option to change it by means of the MAXRECURSION hint. MAXRECURSION hint value can be between 0 to 32,767. Specifying it’s value as 0 means no limit.<\/p>\n
Let us understand MAXRECURSION hint in a Recursive CTE by the below examples:<\/p>\n
CTEs default maximum recursion level<\/strong><\/p>\n
Try to generate numbers between 1 to 200 by using a recursive CTE by the following script:<\/p>\n
\r\nWITH NumbersCTE AS\r\n(\r\n    SELECT  1 AS Number\r\n    UNION ALL\r\n    SELECT Number + 1 FROM NumbersCTE\r\n    WHERE Number < 200\r\n )\r\nSELECT * FROM NumbersCTE<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 530, Level 16, State 1, Line 1
\nThe statement terminated. The maximum recursion 100 has been exhausted before statement completion.<\/p>\n
From the above result it is clear that, the CTEs default maximum recursion level is 100.<\/p>\n
How to change CTEs default maximum recursion level?<\/strong> <\/p>\n
We can change the CTEs default maximum recursion by specifying the MAXRECURSION query hint. Change the previous recursive CTE to generate numbers between 1 to 200 by specifying the MAXRECURSION hint value as 210 as below and verify the result:<\/p>\n
WITH NumbersCTE AS\r\n(\r\n    SELECT  1 AS Number\r\n    UNION ALL\r\n    SELECT Number + 1 FROM NumbersCTE\r\n    WHERE Number < 200\r\n )\r\nSELECT * FROM NumbersCTE\r\nOPTION (MAXRECURSION 210)<\/pre>\n
RESULT:<\/strong>
\n\"Recursive<\/a><\/p>\n
From the above result it is clear that we can change the CTEs default maximum recursion by means of MAXRECURSION query hint.<\/p>\n
MAXRECURSION query hint is helpful to terminate the poorly written recursive CTE<\/strong><\/p>\n
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. For example below is an infinite recursive CTE, but because of the MAXRECURSION hint value 210 it terminates the CTE execution once it reaches the recursion level of 210.<\/p>\n
\r\nWITH NumbersCTE AS\r\n(\r\n    SELECT  1 AS Number\r\n    UNION ALL\r\n    SELECT Number + 1 FROM NumbersCTE   \r\n )\r\nSELECT * FROM NumbersCTE\r\nOPTION (MAXRECURSION 210)<\/pre>\n
RESULT:<\/strong>
\n\"Infinite<\/a>
\nMaximum recursion level that we can specify with MAXRECURSION query hint is 32,767<\/strong><\/p>\n
Try to generate the numbers between 1 to 40000 by the below script, by specifying the MAXRECURSION query hint value as 40000.<\/p>\n
\r\nWITH NumbersCTE AS\r\n(\r\n    SELECT  1 AS Number\r\n    UNION ALL\r\n    SELECT Number + 1 FROM NumbersCTE  \r\n\tWHERE  Number < 40000\r\n )\r\nSELECT * FROM NumbersCTE\r\nOPTION (MAXRECURSION 40000)\r\n<\/pre>\n
RESULT:<\/strong><\/p>\n
Msg 310, Level 15, State 1, Line 9
\nThe value 40000 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.<\/p>\n
From the above result it is clear that the maximum recursion level we can specify with the MAXRECURSION query hint is 32,767. <\/p>\n
How to support the recursion level greater than the maximum recursion level 32,767?<\/strong><\/p>\n
If we come across a scenario, where we need to have recursion level greater than 32,767, in such scenarios we can achieve this by specifying MAXRECURSION value as 0. MAXRECURSION query hint value 0 means no limit to the recusion level, if we are specifying this we should make sure that our query is not resulting in an infinite recursion level. So, we can re-write the previous recursive CTE as below:<\/p>\n
WITH NumbersCTE AS\r\n(\r\n    SELECT  1 AS Number\r\n    UNION ALL\r\n    SELECT Number + 1 FROM NumbersCTE  \r\n\tWHERE  Number < 40000\r\n )\r\nSELECT * FROM NumbersCTE\r\nOPTION (MAXRECURSION 0)<\/pre>\n
RESULT:<\/strong>
\n\"Recursive<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
This is the second article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series: Introduction to Common Table Expression (a.k.a CTE) Multiple CTEs in a Single Query Nested Common Table Expressions Recursive Common Table Expression CTEs provide a mechanism to write easy to understand, … Continue reading Recursive CTE – Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3],"tags":[794,783,807,810,804,805,811,797,802,801,813,799,800,809,803,796,795,321,798,792,793,986,499,808,806,812],"class_list":["post-3948","post","type-post","status-publish","format-standard","hentry","category-sql-server","tag-common-table-expression","tag-cte","tag-cte-maximum-recursion","tag-level-15","tag-level-16","tag-line-1","tag-line-9","tag-maxrecursion","tag-maxrecursion-cte","tag-maxrecursion-hint-in-recursive-cte","tag-maxrecursion-option-exceeds-the-allowed-maximum-of-32767","tag-maxrecursion-sql","tag-maxrecursion-sql-server","tag-msg-310","tag-msg-530","tag-recursive-cte-in-sql-server","tag-recursive-cte-sql","tag-sql","tag-sql-maxrecursion","tag-sql-recursion","tag-sql-recursive-cte","tag-sql-server","tag-state-1","tag-the-maximum-recursion-100-has-been-exhausted-before-statement-completion","tag-the-statement-terminated-the-maximum-recursion-100-has-been-exhausted-before-statement-completion","tag-the-value-40000-specified-for-the-maxrecursion-option-exceeds-the-allowed-maximum-of-32767"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-11G","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3948","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=3948"}],"version-history":[{"count":24,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3948\/revisions"}],"predecessor-version":[{"id":3999,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/3948\/revisions\/3999"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=3948"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=3948"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=3948"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}