Is it possible to change recursion level of a CTE in a function?

Question

I have created a function that takes a string and it returns a table with the position and character of each letter in the string. However as CTE recursion is limited to 100 levels, it fails if a string passed in is longer than that. I know I can use OPTION (MAXRECURSION [value]) to change the limit, but this does not appear to work when defining the function.

My function:

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)    
)
RETURNS TABLE
AS
RETURN
(   
    WITH Split(Pos,Digit)
    AS(
        SELECT 1 AS Pos, LEFT(@String, 1) AS Digit
        UNION ALL
        SELECT Pos + 1, RIGHT(LEFT(@String, Pos + 1), 1)
            FROM Split
            WHERE Pos < LEN(@String)
    )
    SELECT Pos, Digit FROM Split    
    ---OPTION (MAXRECURSION 0) -- Unable to create function if this line is UnCommented)
)

Thus the following sql code will fail due to hitting the 100 default limit:

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')

Now if I add the option, it will work

SELECT * FROM Impact_Work.dbo.StringSplit('How long of a sentence can I possibly think of for testing thingymadoodlers out. Yes I do love the word thingymadoodlers so don''t judge me!')
OPTION (MAXRECURSION 0)

However, it is highly unlikely that those using this function will remember to add this extra bit to their queries so I am hoping to add this in the function it self. Is this possible? (And yes, I do ramble wildly when I need to come up with some string for testing)

Answer 1

This is what Mikael was hinting at, I believe.

You will find things work a lot quicker without that MAX parameter, are you really going to need that big a string?

CREATE FUNCTION [dbo].[StringSplit]
(
    @String NVARCHAR(MAX)    
)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(   
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
 cteTally(N) AS (SELECT TOP (ISNULL(DATALENGTH(@String),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4)
        SELECT N AS Pos, substring(@String, N, 1) AS Digit
        from cteTally
);

Answer 2

As it looks like it is not possible to change the recursion level for a specific function, I've decided to leave it as is for a few reasons. It solves the problem I designed it for and I am adding a note in the function on how people can use it for their queries should they decide to use it for other things.

Answer 3

Actually, the limit of 100 recursions is a default.

From this page: https://msdn.microsoft.com/en-us/library/ms181714.aspx

It says, in part:

MAXRECURSION number (as I see that you have found) says:

Specifies the maximum number of recursions allowed for this query. number is a nonnegative integer between 0 and 32767. When 0 is specified, no limit is applied. If this option is not specified, the default limit for the server 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.

However, recursion may be more expensive than other methods.

Note: This does not need to be a server wide setting, since it can be a query hint. If you have your own function for this string parsing, then that function can use MAXRECURSION (0) without affecting other code that may use recursion.