Home Dashboard Directory Help

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


Status: 

Closed
 as Won't Fix Help for as Won't Fix


29
0
Sign in
to vote
Type: Suggestion
ID: 124653
Opened: 12/4/2005 8:24:15 PM
Access Restriction: Public
2
Workaround(s)
view

Description

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.

Details
Sign in to post a comment.
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?

Thanks,
Sivakumar
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).

Regds,
Sivakumar
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, MSFT 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.
Sign in to post a workaround.
Posted by Daniel Stawicki Parz on 9/13/2012 at 7:46 AM
Add CTE to VIEW withouth OPTION and use OPTION when you call VIEW in some select.

CREATE VIEW CTE_OPTION
AS
WITH CTE (A)
AS
(
SELECT 1 AS A
UNION ALL
SELECT A+1 A FROM CTE WHERE A<10000
)
Select A from CTE
GO

then use select like this

select A from CTE_OPTION
OPTION (MAXRECURSION 10000)
Posted by Pawel Potasinski, MSFT on 8/16/2009 at 10:44 PM
If you know the maximum number of iterations you can play with several CTEs and UNIONs. Example:

WITH CTE1 AS
(
SELECT 1 AS a
UNION ALL
SELECT (a + 1)
FROM CTE1
WHERE a <= 100
), CTE2 AS
(
SELECT MAX(a) AS a
FROM CTE1
UNION ALL
SELECT (a + 1)
FROM CTE2
WHERE a < 180
)
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2