对于某些类型的sql查询，数字辅助表可能非常有用。可以将其创建为具有特定任务所需行数的表，也可以将其创建为返回每个查询所需行数的用户定义函数。

创建此类功能的最佳方法是什么？

解决方案

回答

最佳功能是使用表而不是函数。使用函数会导致额外的CPU负载，从而为返回的数据创建值，尤其是在返回的值覆盖很大范围的情况下。

回答

本文提供了14种不同的可能解决方案，并对每种解决方案进行了讨论。重要的一点是：

suggestions regarding efficiency and
  performance are often subjective.
  Regardless of how a query is being
  used, the physical implementation
  determines the efficiency of a query.
  Therefore, rather than relying on
  biased guidelines, it is imperative
  that you test the query and determine
  which one performs better.

我个人喜欢：

WITH Nbrs ( n ) AS (
    SELECT 1 UNION ALL
    SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

回答

嘿...抱歉，我这么晚才回复一个旧帖子。而且，是的，我必须做出响应，因为该线程上最流行的答案(当时是带有14种不同方法的链接的递归CTE答案)，嗯……性能面临的挑战最多。

首先，具有14种不同解决方案的文章非常适合查看动态创建Numbers / Tally表的不同方法，但是正如文章和所引用的线程中指出的那样，有一个非常重要的报价...

"suggestions regarding efficiency and
  performance are often subjective.
  Regardless of how a query is being
  used, the physical implementation
  determines the efficiency of a query.
  Therefore, rather than relying on
  biased guidelines, it is imperative
  that you test the query and determine
  which one performs better."

具有讽刺意味的是，本文本身包含许多主观陈述和"有偏见的准则"，例如"递归CTE可以非常有效地生成数字列表"和"这是从Itzik Ben-Gen发布的新闻组中使用WHILE循环的一种有效方法"(我确定他发布的只是为了比较)。来吧伙计们...刚提到Itzik的好名声，可能会导致一些可怜的家伙实际使用这种可怕的方法。作者应该练习所讲的内容，并在做这样荒谬的错误陈述之前进行一点性能测试，尤其是面对任何可扩展性时。

考虑到在对任何代码的作用或者某人"喜欢"的内容做出任何主观声明之前实际进行一些测试，我们可以使用以下代码进行自己的测试。为我们要运行测试的SPID设置配置文件分析器，并自己检查一下...只需对数字1000000进行"搜索"替换，以获取"收藏夹"编号，然后查看...

--===== Test for 1000000 rows ==================================
GO
--===== Traditional RECURSIVE CTE method
   WITH Tally (N) AS 
        ( 
         SELECT 1 UNION ALL 
         SELECT 1 + N FROM Tally WHERE N < 1000000 
        ) 
 SELECT N 
   INTO #Tally1 
   FROM Tally 
 OPTION (MAXRECURSION 0);
GO
--===== Traditional WHILE LOOP method
 CREATE TABLE #Tally2 (N INT);
    SET NOCOUNT ON;
DECLARE @Index INT;
    SET @Index = 1;
  WHILE @Index <= 1000000 
  BEGIN 
         INSERT #Tally2 (N) 
         VALUES (@Index);
            SET @Index = @Index + 1;
    END;
GO
--===== Traditional CROSS JOIN table method
 SELECT TOP (1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS N
   INTO #Tally3
   FROM Master.sys.All_Columns ac1
  CROSS JOIN Master.sys.ALL_Columns ac2;
GO
--===== Itzik's CROSS JOINED CTE method
   WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
        E02(N) AS (SELECT 1 FROM E00 a, E00 b),
        E04(N) AS (SELECT 1 FROM E02 a, E02 b),
        E08(N) AS (SELECT 1 FROM E04 a, E04 b),
        E16(N) AS (SELECT 1 FROM E08 a, E08 b),
        E32(N) AS (SELECT 1 FROM E16 a, E16 b),
   cteTally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E32)
 SELECT N
   INTO #Tally4
   FROM cteTally
  WHERE N <= 1000000;
GO
--===== Housekeeping
   DROP TABLE #Tally1, #Tally2, #Tally3, #Tally4;
GO

在此过程中，这是我从SQL Profiler获得的数字，分别为100、1000、10000、100000和1000000。

SPID TextData                                 Dur(ms) CPU   Reads   Writes
---- ---------------------------------------- ------- ----- ------- ------
  51 --===== Test for 100 rows ==============       8     0       0      0
  51 --===== Traditional RECURSIVE CTE method      16     0     868      0
  51 --===== Traditional WHILE LOOP method CR      73    16     175      2
  51 --===== Traditional CROSS JOIN table met      11     0      80      0
  51 --===== Itzik's CROSS JOINED CTE method        6     0      63      0
  51 --===== Housekeeping   DROP TABLE #Tally      35    31     401      0

  51 --===== Test for 1000 rows =============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method      47    47    8074      0
  51 --===== Traditional WHILE LOOP method CR      80    78    1085      0
  51 --===== Traditional CROSS JOIN table met       5     0      98      0
  51 --===== Itzik's CROSS JOINED CTE method        2     0      83      0
  51 --===== Housekeeping   DROP TABLE #Tally       6    15     426      0

  51 --===== Test for 10000 rows ============       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method     434   344   80230     10
  51 --===== Traditional WHILE LOOP method CR     671   563   10240      9
  51 --===== Traditional CROSS JOIN table met      25    31     302     15
  51 --===== Itzik's CROSS JOINED CTE method       24     0     192     15
  51 --===== Housekeeping   DROP TABLE #Tally       7    15     531      0

  51 --===== Test for 100000 rows ===========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method    4143  3813  800260    154
  51 --===== Traditional WHILE LOOP method CR    5820  5547  101380    161
  51 --===== Traditional CROSS JOIN table met     160   140     479    211
  51 --===== Itzik's CROSS JOINED CTE method      153   141     276    204
  51 --===== Housekeeping   DROP TABLE #Tally      10    15     761      0

  51 --===== Test for 1000000 rows ==========       0     0       0      0
  51 --===== Traditional RECURSIVE CTE method   41349 37437 8001048   1601
  51 --===== Traditional WHILE LOOP method CR   59138 56141 1012785   1682
  51 --===== Traditional CROSS JOIN table met    1224  1219    2429   2101
  51 --===== Itzik's CROSS JOINED CTE method     1448  1328    1217   2095
  51 --===== Housekeeping   DROP TABLE #Tally       8     0     415      0

如我们所见，递归CTE方法的持续时间和CPU仅次于While循环，倒数第二差，并且逻辑读取形式的内存压力是While循环的8倍。它是类固醇的RBAR，对于任何单行计算都应不惜一切代价避免，就像应避免While循环一样。在某些地方，递归非常有价值，但是这不是其中之一。

作为侧边栏，Denny先生绝对是位。。。正确大小的永久Numbers或者Tally表是大多数事情的解决之道。正确大小是什么意思？好吧，大多数人都使用Tally表来生成日期或者对VARCHAR(8000)进行拆分。如果我们使用正确的聚集索引创建一个11,000行Tally表，并且在" N"上具有正确的聚集索引，那么我们将有足够的行来创建价值30年以上的日期(我从事抵押贷款的工作相当多，所以30年对我来说是关键数字)，并且足以处理VARCHAR(8000)拆分。为什么"正确调整大小"如此重要？如果经常使用Tally表，则它很容易放入高速缓存中，这使其速度非常快，而对内存没有太大压力。

最后但并非最不重要的一点是，每个人都知道，如果我们创建一个永久的Tally表，那么使用哪种方法来构建它并不重要，因为1)它只会被创建一次； 2)如果它像11,000行表中，所有方法都将"足够好"地运行。那么，为什么我全都对使用哪种方法感到困惑呢？

答案是，一些不了解任何情况而只需要完成工作的可怜人/女孩可能会看到类似递归CTE方法的东西，并决定将其用于比建筑更大型，更频繁使用的东西。一个永久的Tally表，我正在尝试保护这些人，他们的代码在其上运行的服务器以及拥有这些服务器上数据的公司。是的，这很重要。它也应该适合其他所有人。教正确的做事方式，而不是"足够好"。在发布或者使用帖子或者书中的东西之前进行一些测试……实际上，我们挽救的生命可能是我们自己的，尤其是如果我们认为递归CTE是实现此类目标的方法时。 ;-)

谢谢收听...