在T-SQL（SQL Server）中，我可以在函数内部使用OPTION子句吗？

huangapple
go评论116阅读模式
英文:

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

问题

我想编写一个类似于以下的函数：

  1. CREATE OR ALTER FUNCTION TestFunction()
  2. RETURNS TABLE
  3. AS RETURN
  4. WITH NumberList AS (
  5.     SELECT  1 AS Number
  6.     UNION ALL
  7.     SELECT  Number + 1
  8.     FROM    NumberList
  9.     WHERE   Number < 1000
  10. )
  11. SELECT  Number
  12. FROM    NumberList
  13. OPTION (MAXRECURSION 0)

然而，当运行此查询时，SQL Server 返回以下错误：

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

如果我从函数中删除行 OPTION (MAXRECURSION 0)，它可以正常工作：

  1. CREATE OR ALTER FUNCTION TestFunction()
  2. RETURNS TABLE
  3. AS RETURN
  4. WITH NumberList AS (
  5.     SELECT  1 AS Number
  6.     UNION ALL
  7.     SELECT  Number + 1
  8.     FROM    NumberList
  9.     WHERE   Number < 1000
  10. )
  11. SELECT  Number
  12. FROM    NumberList

但是，当执行此函数时：

  1. SELECT  *
  2. FROM    TestFunction()

然后它返回以下错误：

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

是否可能在函数内部添加子句 OPTION (MAXRECURSION 0)？我认为这对于函数的用户来说很方便，不必在执行函数时记住添加选项子句。

英文:

I want to write a function like this:

  1. CREATE OR ALTER FUNCTION TestFunction()
  2. RETURNS TABLE
  3. AS RETURN
  4. WITH NumberList AS (
  5.     SELECT  1 AS Number
  6.     UNION ALL
  7.     SELECT  Number + 1
  8.     FROM    NumberList
  9.     WHERE   Number < 1000
  10. )
  11. SELECT  Number
  12. FROM    NumberList
  13. OPTION (MAXRECURSION 0)

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

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

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

  1. CREATE OR ALTER FUNCTION TestFunction()
  2. RETURNS TABLE
  3. AS RETURN
  4. WITH NumberList AS (
  5.     SELECT  1 AS Number
  6.     UNION ALL
  7.     SELECT  Number + 1
  8.     FROM    NumberList
  9.     WHERE   Number < 1000
  10. )
  11. SELECT  Number
  12. FROM    NumberList

But when executing this function:

  1. SELECT  *
  2. FROM    TestFunction()

Then it returns the following error:

  1. Msg 530, Level 16, State 1, Line 1
  2. 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.

答案1

得分: 3

使用递归生成数字系列会有相当大的开销，可能是其中最慢的方式之一。

在 SQL Server 2022 之前，生成数字系列的绝对最快方式是使用派生值表的交叉连接。这甚至比从物理表中选择整数更快，也是一种常见的选项。

以下将为您提供确切的 1000 行。如果您想要更少的行，您可以在从中选择时使用 top，或者在调用它时传入一个用于 top 的参数。

  1. create function dbo.GenerateSeries()
  2. returns table
  3. as return
  4. with 
  5. 	t1(n) as (select n from(values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n(n)), 
  6. 	t2(n) as (select 0 from t1 x cross join t1 y),
  7. 	t3(n) as (select 0 from t2 x cross join t1 y), 
  8. 	t(n) as (select Row_Number() over (order by (select null)) from t3)
  9. select n
  10. from t;
英文:

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.

  1. create function dbo.GenerateSeries()
  2. returns table
  3. as return
  4. with 
  5. 	t1(n) as (select n from(values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))n(n)), 
  6. 	t2(n) as (select 0 from t1 x cross join t1 y),
  7. 	t3(n) as (select 0 from t2 x cross join t1 y), 
  8. 	t(n) as (select Row_Number() over (order by (select null)) from t3)
  9. select n
  10. from t;

答案2

得分: 1

以下是翻译好的部分：

我认为生成从1到1000的更好方法是使用master..spt_values如下：

  1. CREATE OR ALTER FUNCTION TestFunction()
  2. RETURNS TABLE
  3. AS RETURN
  5. WITH NumberList AS (
  6.   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
  7.   FROM master..spt_values
  8. )
  9. SELECT Number
  10. FROM NumberList
  11. WHERE Number <= 1000;
  1. select * from  TestFunction()
  1. +------------------------------------+
  2. |               Number               |
  3. +------------------------------------+
  4. | 1                                  |
  5. | 2                                  |
  6. | 3                                  |
  7. | ... --4 到 995 被省略以缩短长度 |
  8. | 996                                |
  9. | 997                                |
  10. | 998                                |
  11. | 1000                               |
  12. +------------------------------------+

fiddle

英文:

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

  1. CREATE OR ALTER FUNCTION TestFunction()
  2. RETURNS TABLE
  3. AS RETURN
  4.   
  5. WITH NumberList AS (
  6.   SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Number
  7.   FROM master..spt_values
  8. )
  9. SELECT Number
  10. FROM NumberList
  11. WHERE Number <= 1000;
  2. select * from  TestFunction()
  1. +------------------------------------+
  2. |               Number               |
  3. +------------------------------------+
  4. | 1                                  |
  5. | 2                                  |
  6. | 3                                  |
  7. | ... --4 to 995 omitted for brevity |
  8. | 996                                |
  9. | 997                                |
  10. | 998                                |
  11. | 1000                               |
  12. +------------------------------------+

fiddle

huangapple
  • 本文由 发表于 2023年8月5日 14:15:38
  • 转载请务必保留本文链接：https://go.coder-hub.com/76840377.html
  • sql
  • sql-function
  • sql-server
  • sql-server-2019
  • tsql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定