June 22, 2015 at 11:06 am
I have a recursive CTE on an inline table valued function. I need to set the MAXRECURSION option on the CTE, but SQL Server is complaining with "Incorrect syntax near the keyword 'OPTION'".
It works fine on non-inline function. I couldn't find any documentation indicating this wasn't possible.
I can use the MAXRECURSION option in call to the function
SELECT * FROM MyFunction ()
OPTION ( MAXRECURSION 0 )
but that means that the user needs to know the "MyFunction" uses recursive CTE, which defeats the purpose of the abstraction.
Any hints on putting the option inside the function is appreciated.
June 22, 2015 at 11:53 am
Why do you need a recursive CTE with more than 100 recursions? That's a key to bad performance or an indication of a flaw in your design or data.
June 22, 2015 at 12:15 pm
I appreciate your comment, but you still didn't answer my question. Anyhow, there are many reasons you need a recursive CTE where maxrecursion > 100. A sequential list of numbers is one of them, and CTE is a very fast way to generate that.
create function SequenceList ( @variable int )
returns table
as
return (
with cte as
(
select id = 1
union all
select id = cte.id+1
from cte
where id < @variable
)
select id from cte
option ( maxrecursion 0 )
)
where @variable is the input parameter to a function. if @variable is > 100 and the function is an inline table valued function, SQL server is throwing an error. Of course, I'm not generating a sequential list of numbers, that was just an example.
June 22, 2015 at 12:31 pm
You're right, a CTE is a very fast way to generate a sequence of numbers. However, a recusive CTE is a very slow approach to get it. Jeff Moden wrote an article on it and I suggest you to read it. http://www.sqlservercentral.com/articles/T-SQL/74118/
For an alternative, check the following function:
/**********************************************************************************************************************
Purpose:
Return a sequence of consecutive numbers from 1 to N
**********************************************************************************************************************/
CREATE FUNCTION [dbo].[SequenceList]
--===== Define I/O parameters
(@pLimit int)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
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
--==== This provides the list of numbers
SELECT TOP (@pLimit) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) id
FROM E4;
So basically, I understand that you might think you need a recursive CTE, but you probably don't need it. Recursion on a CTE can generate bad estimates that will affect the execution plan and can also kill your tempdb.
June 22, 2015 at 12:33 pm
N_Muller (6/22/2015)
I appreciate your comment, but you still didn't answer my question. Anyhow, there are many reasons you need a recursive CTE where maxrecursion > 100. A sequential list of numbers is one of them, and CTE is a very fast way to generate that.create function SequenceList ( @variable int )
returns table
as
return (
with cte as
(
select id = 1
union all
select id = cte.id+1
from cte
where id < @variable
)
select id from cte
option ( maxrecursion 0 )
)
where @variable is the input parameter to a function. if @variable is > 100 and the function is an inline table valued function, SQL server is throwing an error. Of course, I'm not generating a sequential list of numbers, that was just an example.
Just generating a list of numbers from 1 to some input value?
No recursion needed.
create function SequenceList ( @variable int )
returns table
as
return (
with
e1(n) as (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
e2(n) as (select 1 from e1 a cross join e1 b),
e4(n) as (select 1 from e2 a cross join e2 b),
eTally(n) as (select top (@variable) row_number() over (order by (select null)) from e4 a cross join e2 b)
select n as id from eTally);
June 22, 2015 at 12:36 pm
Luis Cazares (6/22/2015)
...
Just an FYI, with SQL Server 2008 and newer you don't need all those UNION ALLs.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply