对于某些类型的sql查询，数字辅助表可能非常有用。 可以将其创建为具有特定任务所需行数的表，也可以将其创建为返回每个查询所需行数的用户定义函数。
创建此类功能的最佳方法是什么？
嘿...抱歉，我这么晚才回复一个旧帖子。而且，是的，我必须做出回应，因为该线程上最受欢迎的答案(当时是递归CTE答案，其中包含14种不同方法的链接)，嗯……性能受到了最大的挑战。
首先，具有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进行"搜索"替换，以获取"收藏夹"编号，然后查看...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
| --===== 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 ...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
| 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是实现此类目标的方法时。 ;-)
感谢收听...
最佳功能将是使用表而不是函数。 使用函数会导致额外的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.
我个人喜欢：
1
2
3
4
5
| WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 ) |
此视图超快，并且包含所有正int值。
1
2
3
4
5
6
7
8
9
10
11
12
| CREATE VIEW dbo.Numbers
WITH SCHEMABINDING
AS
WITH Int1(z) AS (SELECT 0 UNION ALL SELECT 0)
, Int2(z) AS (SELECT 0 FROM Int1 a CROSS JOIN Int1 b)
, Int4(z) AS (SELECT 0 FROM Int2 a CROSS JOIN Int2 b)
, Int8(z) AS (SELECT 0 FROM Int4 a CROSS JOIN Int4 b)
, Int16(z) AS (SELECT 0 FROM Int8 a CROSS JOIN Int8 b)
, Int32(z) AS (SELECT TOP 2147483647 0 FROM Int16 a CROSS JOIN Int16 b)
SELECT ROW_NUMBER() OVER (ORDER BY z) AS n
FROM Int32
GO |
使用SQL Server 2016+生成数字表，可以使用OPENJSON：
1
2
3
4
5
| -- range from 0 to @max - 1
DECLARE @MAX INT = 40000;
SELECT rn = CAST([KEY] AS INT)
FROM OPENJSON(CONCAT('[1', REPLICATE(CAST(',1' AS VARCHAR(MAX)),@max-1),']')); |
LiveDemo
取材于我们如何使用OPENJSON生成一系列数字？
再后来，我想贡献一个略有不同的"传统" CTE(不接触基表以获取行的数量)：
1
2
3
4
5
6
7
| --===== Hans CROSS JOINED CTE method
WITH Numbers_CTE (Digit)
AS
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9)
SELECT HundredThousand.Digit * 100000 + TenThousand.Digit * 10000 + Thousand.Digit * 1000 + Hundred.Digit * 100 + Ten.Digit * 10 + One.Digit AS NUMBER
INTO #Tally5
FROM Numbers_CTE AS One CROSS JOIN Numbers_CTE AS Ten CROSS JOIN Numbers_CTE AS Hundred CROSS JOIN Numbers_CTE AS Thousand CROSS JOIN Numbers_CTE AS TenThousand CROSS JOIN Numbers_CTE AS HundredThousand |
此CTE比Itzik的CTE执行更多的读取，但不如传统的CTE执行更多的读取。
但是，它始终执行比其他查询更少的写操作。
如您所知，写入始终比读取昂贵得多。
持续时间在很大程度上取决于核心数(MAXDOP)，但在我的8核心上，其执行速度始终比其他查询一致(以毫秒为单位)。
我在用：
1
2
3
4
| Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) ON Windows NT 6.3 <X64> (Build 9600: ) |
在Windows Server 2012 R2上，32 GB，Xeon X3450 @ 2.67Ghz，启用4核HT。
编辑：请参阅下面的康拉德评论。
杰夫·摩登的答案很棒……但是我在Postgres上发现，除非您删除E32行，否则Itzik方法将失败。
在postgres上速度稍快(40ms与100ms)是我在此处发现的适用于postgres的另一种方法：
1
2
3
4
5
6
7
8
9
10
11
12
13
| WITH
E00 (N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ),
E01 (N) AS (SELECT a.N FROM E00 a CROSS JOIN E00 b),
E02 (N) AS (SELECT a.N FROM E01 a CROSS JOIN E01 b ),
E03 (N) AS (SELECT a.N FROM E02 a CROSS JOIN E02 b
LIMIT 11000 -- end record 11,000 good for 30 yrs dates
), -- max is 100,000,000, starts slowing e.g. 1 million 1.5 secs, 2 mil 2.5 secs, 3 mill 4 secs
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY a.N) FROM E03 a)
SELECT N
FROM Tally |
当我从SQL Server迁移到Postgres世界时，可能已经错过了在该平台上做统计表的更好方法了……INTEGER()？序列()？
