创建的最佳方法是什么这样的功能？
#1 楼
嘿...抱歉，我这么晚才回复一个旧帖子。而且，是的，我不得不做出回应，因为该线程上最受欢迎的答案（当时是递归CTE答案，其中包含14种不同方法的链接），这是对……性能的最大挑战。首先，本文提供了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和Loop持续时间和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是实现此类目标的方法时。 ;-)
感谢您的收听...
评论
我真的很希望更多的人对你有社会责任感。曾经说过，除了需要为所有种类的东西填充一个Numbers表之外，如果出于某种原因，似乎具有IDENTITY的SELECT INTO比CTE更快。
–安德烈·菲格雷多（Andre Figueiredo）
17年7月16日在3:09
谢谢您的宝贵意见，安德烈。
–杰夫·摩登（Jeff Moden）
17年7月23日在0:31
#2 楼
最佳功能将是使用表而不是函数。使用函数会导致额外的CPU负载，从而为返回的数据创建值，尤其是在返回的值覆盖很大范围的情况下。评论
我认为这取决于您的情况。在两个性能最佳的选项之间，您可以在IO和CPU成本之间进行权衡，这取决于对您而言更昂贵的成本。
– Rbjz
13年6月18日在10:19
IO几乎总是比CPU便宜，特别是因为此表很小，可能已经在budferpool中了。
–mrdenny
13年6月18日在11:53
@mrdenny I / O总是比CPU更昂贵，更慢。近年来，SSD有所改变，但是在大多数生产架构中，这些SSD在它们和CPU之间具有网络链接。我看到的唯一真正受CPU约束的数据库正在运行未经调整的仅ORM的应用程序或繁重的机器学习。
– rmalayter
17年11月21日在4:25
@rmalayter除非表的使用频率足以让我们照顾，否则它几乎肯定会存在于内存中，并且内存的升级成本较低，并且通常不会影响添加CPU内核的许可方式。 SQL Server企业版将以每位数5位数字的价格处于困境，即仅在许可方面添加内核可能会比在服务器中投入更多内存的全部成本花费更多。
–狗
19年2月14日在16:31
#3 楼
本文给出了14种不同的可能解决方案，并对每种解决方案进行了讨论。重要的一点是：关于效率和性能的建议通常是主观的。无论查询是如何使用的，物理的实施方式
确定查询的效率。
因此，不是依赖于
有偏见的准则，必须测试测试并确定
执行哪个查询。更好。
我个人喜欢：
WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )
评论
被接受的答案证明是错误的吗？尽管看起来很帅，但它不是“最佳”的。
– Rbjz
13年6月18日在10:17
#4 楼
此视图超快，包含所有正的int值。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
评论
0通常很有用。我可能会将最后一列转换为int。您还应该知道，该方法基本上是以Itzik的CROSS JOINED CTE方法的名称包含在接受的答案中（既不包含0，也不转换为int）。
– Andriy M
2011年7月4日在21:02
在视图中添加WITH SCHEMABINDING的任何特殊原因？
–ca9163d9
2012-2-27在22:12
添加“ WITH SCHEMABINDING”可以使查询更快。它可以帮助优化器知道没有数据被访问。 （请参阅blogs.msdn.com/b/sqlprogrammability/archive/2006/05/12/…）
–安东尼·法尔
2012年2月28日在8:07
我想知道@AnthonyFaull是否可以通过一些度量来支持这一点。
– Rbjz
13年6月18日在10:22
#5 楼
使用SQL Server 2016+生成数字表，您可以使用OPENJSON：-- 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 想法取自How can我们使用OPENJSON生成一系列数字吗？
评论
真好我猜想，如果SQL Server的XQuery已完全支持position（），则可能会使用与此类似的XML。
– Andriy M
16年5月2日在18:06
抱歉，您的评论太迟了，但是与Itik的级联CTE方法相比，该代码使用的CPU多了11.4倍，逻辑读数（2,000,023）无限多。
–杰夫·摩登（Jeff Moden）
18年9月5日在18:01
#6 楼
编辑：请参阅下面的康拉德（Conrad）的评论。杰夫·摩登（Jeff Moden）的回答很好...但是我在Postgres上发现，除非您删除E32行，否则Itzik方法将失败。
稍快一点在postgres上（40ms与100ms）是我在此处找到的适用于postgres的另一种方法：
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（）？ SEQUENCE（）？
评论
可能错过了在[postgres]上做理算表的更好方法，是的，您确实generate_series
–康拉德·弗里克斯（Conrad Frix）
2013年1月24日21:07
@Conrad Frix，对于这个很晚的问题（晚了5年以上）表示歉意，但是您是否进行了性能测试以将该出色的内置工具与其他方法进行比较？
–杰夫·摩登（Jeff Moden）
18-09-5在18:02
@JeffModen对不起，但是很容易测试。以Ruskin的查询为例进行比较，并调用以生成序列。
–康拉德·弗里克斯（Conrad Frix）
18-09-5在20:06
@Conrad Frix，因为您宣称性能，并且可以访问这两种环境（我没有），并且您也声称它易于测试，所以我希望您花些时间对其进行测试。 ;-)
–杰夫·摩登（Jeff Moden）
'18 Sep 6'在22:39
@Conrad Frix，嘿...您已经设置好了，您无需花费5分钟即可测试自己的性能要求。 NP。继续，
–杰夫·摩登（Jeff Moden）
18年9月8日，0：31
#7 楼
再后来，我想贡献一个略有不同的“传统” CTE（不接触基表以获取行的数量）：--===== 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核上，其执行速度始终比其他查询始终更快（以毫秒为单位的持续时间较短）。
我正在使用：
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。
评论
您能解释为什么这样做而不是使用预先填充数字的表格吗？例如，要填写这样的表格。
并非所有DBA和/或第三方应用程序都允许添加永久表。
在https://feedback.azure.com/forums/908035-sql-server/suggestions/32890519-add-a-built-in-table-上投票以使用不会浪费内存和IO的内置虚拟数字表功能数