Can I use an OPTION clause inside a function in T-SQL (SQL Server)?

Question

I want to write a function like this:

CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
WITH NumberList AS (
    SELECT  1 AS Number
    UNION ALL
    SELECT  Number + 1
    FROM    NumberList
    WHERE   Number < 1000
)
SELECT  Number
FROM    NumberList
OPTION (MAXRECURSION 0)

However, when running this query, SQL Server returns the following error:

Msg 156, Level 15, State 1, Procedure TestFunction, Line 13 [Batch Start Line 0]
Incorrect syntax near the keyword 'OPTION'.

If I remove the row OPTION (MAXRECURSION 0) from the function it works:

CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
WITH NumberList AS (
    SELECT  1 AS Number
    UNION ALL
    SELECT  Number + 1
    FROM    NumberList
    WHERE   Number < 1000
)
SELECT  Number
FROM    NumberList

But when executing this function:

SELECT  *
FROM    TestFunction()

Then it returns the following error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Is it possible to add the clause OPTION (MAXRECURSION 0) inside the function? I think it would be convenient for the user of the function not having to remember to add the option clause when executing the function.

add the `OPTION (MAXRECURSION 0)` to your final query, not the view. `SELECT * FROM TestFunction() OPTION (MAXRECURSION 0)` — Squirrel, Aug 05 '23 at 06:31
That's not a great way to generate a numbers table BTW. You have an XY problem — Mitch Wheat, Aug 05 '23 at 07:08
A tally would be a much better way; it's faster and doesn't require recursion. If you're on 2022+ then use `GENERATE_SERIES`. — Thom A, Aug 05 '23 at 08:10

Stu · Answer 1 · 2023-08-05T08:49:49.540

Using recursion to generate a number series has considerable overhead and is probably one of the slowest possible ways to do it.

The absolute fastest way, prior to SQL Server 2022, to generate a number series would be to use a cross join using a derived table of values. This is faster than even select integers from a physical table, also a common option.

The following will give you exactly 1000 rows. If you wanted fewer rows you could use top when you select from it, or pass in a parameter to use for top when you invoke it.

create function dbo.GenerateSeries()
returns table
as return
with 
    t1(n) as (select n from(values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n(n)), 
    t2(n) as (select 0 from t1 x cross join t1 y),
    t3(n) as (select 0 from t2 x cross join t1 y), 
    t(n) as (select Row_Number() over (order by (select null)) from t3)
select n
from t;

Some details on the "considerable overhead" https://stackoverflow.com/a/70500904 — Martin Smith, Aug 05 '23 at 14:03

score 1 · Accepted Answer · edited Aug 05 '23 at 13:57

1

I think the better approach to generate numbers from 1 to 1000 will be using master..spt_values as below:

CREATE OR ALTER FUNCTION TestFunction()
RETURNS TABLE
AS RETURN
  
WITH NumberList AS (
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
  FROM master..spt_values
)
SELECT Number
FROM NumberList
WHERE Number <= 1000;


select * from  TestFunction()

+------------------------------------+
|               Number               |
+------------------------------------+
| 1                                  |
| 2                                  |
| 3                                  |
| ... --4 to 995 omitted for brevity |
| 996                                |
| 997                                |
| 998                                |
| 1000                               |
+------------------------------------+

fiddle

edited Aug 05 '23 at 13:57

Martin Smith

438,706
87
741
845

answered Aug 05 '23 at 08:09

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

5

We really don't need that massive markdown table... – Thom A Aug 05 '23 at 08:11
1

My `master..spt_values` table contains 2552 rows, so for my case it's a great solution. Thanks! – Daniel Jonsson Aug 05 '23 at 11:36
1

Just beware that selecting from master..spt_values is slow in comparison to the cross-join values table constructor method. – Stu Aug 05 '23 at 12:55
@DanielJonsson - You can just do `SELECT number FROM master..spt_values where type = N'P' and number BETWEEN 1 AND 1000` because it already has those numbers. – Martin Smith Aug 05 '23 at 13:49

Can I use an OPTION clause inside a function in T-SQL (SQL Server)?

2 Answers2