Allow MAXRECURSION limit values other than 100 for views and UDFs - by Steve Kass

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 124653 Comments
Status Closed Workarounds
Type Suggestion Repros 6
Opened 12/4/2005 8:24:15 PM
Access Restriction Public


CTEs are a powerful new feature of SQL Server 2005.  

Unfortunately, there is no way to specify the MAXRECURSION limit in a recursive view or UDF definition, since OPTION (MAXRECURSION limit) is not allowed in view and UDF definitions, and there
is no global MAXRECURSION setting at any level.

As a result, it is not possible to implement
something like CONNECT BY in a view or UDF without being limited to 100
levels of recursion.  

Sign in to post a comment.
Posted by CPABARABOO on 4/4/2016 at 2:40 PM
Please consider re-opening this. There should be a setting in sys.configurations (or somewhere) where this can be set.
Posted by Kumar R V S on 8/25/2014 at 5:06 AM
MS Team -

Can I request for the reopen of this defect?

Posted by Kumar R V S on 8/25/2014 at 4:28 AM
Also would like to let you know this is not specific to ONLY UDF, and is generic to any procedure using CTE's. We have to explicitly mention the MAXRECURSION query hint in the query, and we have such CTE queries used across the application and is not worth modifying all the locations.

Please let us know if we can set this at the server level or database level.
Posted by Kumar R V S on 8/25/2014 at 4:24 AM
Hello MS Team,

I have gone through the work-arounds on this, but is the "Max Recursion" option available in the Database/Server level, instead of modifying any code in the existing system.

Would definitely help if you can let us know on this, so the setting of 1000 can be set at the database level instead of modifying the code or setting the query hint of (maxrecursion of 100).

Posted by DB007 on 3/18/2011 at 8:58 AM
Why wont fix????
Posted by eralper on 10/7/2010 at 12:27 AM
The MaxRecursion problem within UDF still exists in SQL Server 2008 R2.
Developers should turn to use Procedure instead of UDF Functions
Posted by Gary271 on 5/30/2010 at 1:41 AM
Hello Microsoft SQL Team - We are a Microsoft Gold Certified Partner Dynamics ERP/CRM + ISV - M4 Systems Ltd - This bug has NOT been fixed with 2008 R2 and is a massive problem for us and our clients - We need to increase the limit above 100 within a UDF because we are calling UDFs from Computed Columns and Table Constraints - I can find no way to specify OPTION (MAXRECURSION limit) thats works in such scenarios
Please help ?? Even if limit could be increased at Server Level that would be fine - with modern multi-core processors is crazy to limit to 100 - Thanks Gary Clarke CEO M4 Systems Ltd
Posted by thorn2fish on 9/17/2009 at 8:32 AM
Since one of the main purposes of a view or UDF is to simplify code, and in this case the recursive CTE is somewhat hidden from the downstream developer, it can be very confusing and adds complexity to specify the option in the downstream SQL. It needs to be able to be set at the view/UDF, database, or global levels.
Posted by Pawel Potasinski on 8/17/2009 at 2:28 AM
Any news on the item? The problem exists in SQL Server 2008. Will it be resolved in R2 (or SQL Server 11)?
Posted by Microsoft on 10/12/2006 at 5:22 PM
Thank you for your feedback
We will consider this in the next release of SQL Server.