Search

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

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)
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 (expand)
Product Language
English
Version
SQL Server 2005 - Developer Edition (32)
Category
SQL Engine
Operating System
Windows XP SP2 Professional
Operating System Language
English
Proposed Solution
Allow OPTION (MAXRECURSION limit) or the equivalent functionality for recursive view and function definitions.
Benefits
Faster Development
Improved Reliability
Improved Performance
Improved Administration
Other Benefits
Faster Development
File Attachments
0 attachments
Sign in to post a comment.
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.
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 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
© 2013 Microsoft Corporation. All rights reserved. Microsoft Connect Terms of Use | Trademarks | Privacy Statement