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.

Answer 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

Answer 2

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;