SQL，辅助数字表

Question

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

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

Answer 1

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

“关于效率和性能的建议通常是主观的。无论如何使用查询，物理实现方式都会决定查询的效率。因此，您必须测试该查询并确定哪个查询，而不是依赖于有偏见的准则。表现更好。”

具有讽刺意味的是，本文本身包含许多主观陈述和“有偏见的准则”，例如“递归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方法仅在While循环的持续时间和CPU方面排名倒数第二，并且其逻辑压力形式的内存压力是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是实现此类目标的方法。;-)

感谢收听…

分享