I've seen many different ways to create and populate a numbers table. However, what is the best way to create and populate one? With "best" being defined from most to least important:
我看到了许多不同的创建和填充数字表的方法。然而，创建和填充一个的最佳方法是什么?从最不重要的地方定义“最佳”:
- Table created with optimal indexing
- 用最优索引创建的表。
- rows generated fastest
- 行生成的最快
- simple code used to create and populate
- 用于创建和填充的简单代码。
If you don't know what a numbers table is, look here: Why should I consider using an auxiliary numbers table?
如果您不知道数字表是什么，那么请看这里:为什么我应该考虑使用辅助数字表?
10 个解决方案
#1
111
here are some code examples taken from the web and from answers to this question.
下面是一些从web上获取的代码示例，以及这个问题的答案。
For Each Method, I have modified the original code so each use the same table and column: NumbersTest and Number, with 10,000 rows or as close to that as possible. Also, I have provided links to the place of origin.
对于每个方法，我都修改了原始代码，因此每个代码都使用相同的表和列:NumbersTest和Number，有10,000行或者尽可能接近这个。此外，我还提供了到原产地的链接。
METHOD 1 here is a very slow looping method from here
avg 13.01 seconds
ran 3 times removed highest, here are times in seconds: 12.42, 13.60
方法1这里是一个非常慢的循环方法从这里avg 13.01秒跑了3次最高，这里是几秒:12.42,13.60。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest(Number INT IDENTITY(1,1))
SET NOCOUNT ON
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 100000
BEGIN
INSERT dbo.NumbersTest DEFAULT VALUES
END
SET NOCOUNT OFF
-- Add a primary key/clustered index to the numbers table
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE())/1000.0)+' seconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 2 here is a much faster looping one from here
avg 1.1658 seconds
ran 11 times removed highest, here are times in seconds: 1.117, 1.140, 1.203, 1.170, 1.173, 1.156, 1.203, 1.153, 1.173, 1.170
方法2，这里是一个更快速的循环，从这里，avg 1.1658秒跑了11次最高，这里是几秒:1.117,1.140,1.203,1.170,1.173,1.156,1.203,1.153,1.173,1.170。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number INT NOT NULL);
DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 10000
BEGIN
INSERT INTO dbo.NumbersTest(Number) VALUES (@i);
SELECT @i = @i + 1;
END;
SET NOCOUNT OFF
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE())/1000.0)+' seconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 3 Here is a single INSERT based on code from here
avg 488.6 milliseconds
ran 11 times removed highest, here are times in milliseconds: 686, 673, 623, 686,343,343,376,360,343,453
方法3这是一个基于代码的单一插入，avg488.6毫秒运行了11次最高，这里是几毫秒:686,673,623,6863343,376,360,343,453。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
;WITH Nums(Number) AS
(SELECT 1 AS Number
UNION ALL
SELECT Number+1 FROM Nums where Number<10000
)
insert into NumbersTest(Number)
select Number from Nums option(maxrecursion 10000)
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 4 here is a "semi-looping" method from here avg 348.3 milliseconds (it was hard to get good timing because of the "GO" in the middle of the code, any suggestions would be appreciated)
ran 11 times removed highest, here are times in milliseconds: 356, 360, 283, 346, 360, 376, 326, 373, 330, 373
4这是一个“semi-looping”方法从这里avg 348.3毫秒(很难得到“走”的好时机,因为中间的代码,任何建议,我们将不胜感激)跑11次最高,时间以毫秒为单位:356,360,283,346,360,376,326,373,330,330
DROP TABLE NumbersTest
DROP TABLE #RunDate
CREATE TABLE #RunDate (RunDate datetime)
INSERT INTO #RunDate VALUES(GETDATE())
CREATE TABLE NumbersTest (Number int NOT NULL);
INSERT NumbersTest values (1);
GO --required
INSERT NumbersTest SELECT Number + (SELECT COUNT(*) FROM NumbersTest) FROM NumbersTest
GO 14 --will create 16384 total rows
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
SELECT CONVERT(varchar(20),datediff(ms,RunDate,GETDATE()))+' milliseconds' FROM #RunDate
SELECT COUNT(*) FROM NumbersTest
METHOD 5 here is a single INSERT from Philip Kelley's answer
avg 92.7 milliseconds
ran 11 times removed highest, here are times in milliseconds: 80, 96, 96, 93, 110, 110, 80, 76, 93, 93
方法5这是菲利普·凯利的一个简单的回答avg92.7毫秒，运行了11次最高，这里是几毫秒:80,96,96,93,110,110,80,76,93,93。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
--I removed Pass5, since I'm only populating the Numbers table to 10,000
Tally as (select row_number() over(order by C) as Number from Pass4)
INSERT NumbersTest
(Number)
SELECT Number
FROM Tally
WHERE Number <= 10000
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 6 here is a single INSERT from Mladen Prajdic answer
avg 82.3 milliseconds
ran 11 times removed highest, here are times in milliseconds: 80, 80, 93, 76, 93, 63, 93, 76, 93, 76
方法6这是一个从Mladen Prajdic回答avg 82.3毫秒的单一插入，运行11次最高，这里是几毫秒:80、80、93、76、93、63、93、76、93、76。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
INSERT INTO NumbersTest(Number)
SELECT TOP 10000 row_number() over(order by t1.number) as N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number);
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 7 here is a single INSERT based on the code from here
avg 56.3 milliseconds
ran 11 times removed highest, here are times in milliseconds: 63, 50, 63, 46, 60, 63, 63, 46, 63, 46
方法7这是一个基于代码的单一插入，avg 56.3毫秒运行11次最高，这里是几毫秒的时间:63,50,63,46,60,63,63,46,63,46。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO NumbersTest
FROM sys.objects s1 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
After looking at all these methods, I really like Method 7, which was the fastest and the code is fairly simple too.
在查看了所有这些方法之后，我非常喜欢方法7，它是最快的，而且代码也是相当简单的。
#2
37
i use this which is fast as hell:
我用这个快得像地狱:
insert into Numbers(N)
select top 1000000 row_number() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2
#3
13
If you're just doing this in SQL Server Management Studio or sqlcmd, you can use the fact that the batch separator allows you to repeat the batch:
如果您只是在SQL Server Management Studio或sqlcmd中这样做，您可以使用批分隔符允许您重复该批处理:
CREATE TABLE Number (N INT IDENTITY(1,1) PRIMARY KEY NOT NULL);
GO
INSERT INTO Number DEFAULT VALUES;
GO 100000
This will insert 100000 records into the Numbers table.
这将在数字表中插入100000条记录。
It's slow. It compares to METHOD 1 in @KM.'s answer, which is the slowest of the examples. However, it's about as code light as it gets. You could speed it up somewhat by adding the primary key constraint after the insert batch.
它是缓慢的。它与@KM中的方法1比较。这是最慢的例子。然而，它是关于代码的光。通过在插入批处理之后添加主键约束，可以稍微加快速度。
#4
9
I start with the following template, which is derived from numerous printings of Itzik Ben-Gan's routine:
我从下面的模板开始，这个模板来自Itzik beno - gan的日常工作的大量打印:
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
select Number from Tally where Number <= 1000000
The "WHERE N<= 1000000" clause limits the output to 1 to 1 million, and can easily be adjusted to your desired range.
“WHERE N<= 1000000”子句将输出限制在1到100万，并且可以很容易地调整到您想要的范围。
Since this is a WITH clause, it can be worked into an INSERT... SELECT... like so:
由于这是一个WITH子句，它可以被工作到插入…选择……像这样:
-- Sample use: create one million rows
CREATE TABLE dbo.Example (ExampleId int not null)
DECLARE @RowsToCreate int
SET @RowsToCreate = 1000000
-- "Table of numbers" data generator, as per Itzik Ben-Gan (from multiple sources)
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT Example (ExampleId)
select Number
from Tally
where Number <= @RowsToCreate
Indexing the table after it's built will be the fastest way to index it.
在表构建后对表进行索引将是索引它的最快方法。
Oh, and I'd refer to it as a "Tally" table. I think this is a common term, and you can find loads of tricks and examples by Googling it.
哦，我把它叫做“理货”表。我认为这是一个常见的术语，你可以通过谷歌搜索找到很多技巧和例子。
#5
3
For anyone looking for an Azure solution
对于任何寻找Azure解决方案的人来说。
SET NOCOUNT ON
CREATE TABLE Numbers (n bigint PRIMARY KEY)
GO
DECLARE @numbers table(number int);
WITH numbers(number) as (
SELECT 1 AS number
UNION all
SELECT number+1 FROM numbers WHERE number<10000
)
INSERT INTO @numbers(number)
SELECT number FROM numbers OPTION(maxrecursion 10000)
INSERT INTO Numbers(n) SELECT number FROM @numbers
Sourced from the sql azure team blog http://azure.microsoft.com/blog/2010/09/16/create-a-numbers-table-in-sql-azure/
来自于sql azure team博客http://azure.microsoft.com/blog/2010/09/16/crea-a -numbers-table-in-sql-azure/。
#6
2
I use numbers tables for primarily dummying up reports in BIRT without having to fiddle around with dynamic creation of recordsets.
我使用数字表主要是为了在BIRT中查找报告，而不需要摆弄记录集的动态创建。
I do the same with dates, having a table spanning from 10 years in the past to 10 years in the future (and hours of the day for more detailed reporting). It's a neat trick to be able to get values for all dates even if your 'real' data tables don't have data for them.
我对日期也做同样的事情，有一张从过去10年到未来10年的表格(以及更详细的报告的时间)。即使你的“真实”数据表没有数据，也可以为所有日期获取值。
I have a script which I use to create these, something like (this is from memory):
我有一个用来创建这些的脚本，比如(这是来自内存):
drop table numbers; commit;
create table numbers (n integer primary key); commit;
insert into numbers values (0); commit;
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
The number of rows doubles with each line so it doesn't take a lot to produce truly huge tables.
每一行的行数增加一倍，因此不需要花费大量的时间来生成真正的大表。
I'm not sure I agree with you that it's important to be created fast since you only create it once. The cost of that is amortized over all the accesses to it, rendering that time fairly insignificant.
我不确定我是否同意你的观点，因为你只创建一次，所以快速创建是很重要的。它的成本分摊到所有对它的访问上，使时间变得无关紧要。
#7
1
Some of the suggested methods are basing on system objects (for example on the 'sys.objects'). They are assuming these system objects contain enough records to generate our numbers.
一些建议的方法基于系统对象(例如，“sys.objects”)。他们假设这些系统对象包含足够的记录来生成我们的数字。
I would not base on anything which does not belong to my application and over which I do not have full control. For example: the content of these sys tables may change, the tables may not be valid anymore in new version of SQL etc.
我不会基于任何不属于我的应用程序和我没有完全控制的东西。例如:这些sys表的内容可能会发生变化，在新版本的SQL中，这些表可能不再有效。
As a solution, we can create our own table with records. We then use that one instead these system related objects (table with all numbers should be fine if we know the range in advance otherwise we could go for the one to do the cross join on).
作为解决方案，我们可以创建自己的表和记录。然后，我们使用那个系统相关的对象(如果我们事先知道了范围，那么所有的数据都应该没问题，否则我们就可以去做交叉连接了)。
The CTE based solution is working fine but it has limits related to the nested loops.
基于CTE的解决方案运行良好，但是它有与嵌套循环相关的限制。
#8
1
Here is a couple of extra methods:
Method 1
这里有一些额外的方法:方法1。
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i int = 1;
INSERT INTO dbo.Numbers (Number)
VALUES (1),(2);
WHILE 2*@i < 1048576
BEGIN
INSERT INTO dbo.Numbers (Number)
SELECT Number + 2*@i
FROM dbo.Numbers;
SET @i = @@ROWCOUNT;
END
GO
SELECT COUNT(*) FROM Numbers AS RowCownt --1048576 rows
Method 2
方法2
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i INT = 0;
INSERT INTO dbo.Numbers (Number)
VALUES (1);
WHILE @i <= 9
BEGIN
INSERT INTO dbo.Numbers (Number)
SELECT N.Number + POWER(4, @i) * D.Digit
FROM dbo.Numbers AS N
CROSS JOIN (VALUES(1),(2),(3)) AS D(Digit)
ORDER BY D.Digit, N.Number
SET @i = @i + 1;
END
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
Method 3
方法3
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int identity NOT NULL PRIMARY KEY, T bit NULL);
WITH
T1(T) AS (SELECT T FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(T)) --10 rows
,T2(T) AS (SELECT A.T FROM T1 AS A CROSS JOIN T1 AS B CROSS JOIN T1 AS C) --1,000 rows
,T3(T) AS (SELECT A.T FROM T2 AS A CROSS JOIN T2 AS B CROSS JOIN T2 AS C) --1,000,000,000 rows
INSERT INTO dbo.Numbers(T)
SELECT TOP (1048576) NULL
FROM T3;
ALTER TABLE Numbers
DROP COLUMN T;
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
Method 4, taken from Defensive Database Programming book by Alex Kuznetsov
方法4，取自亚历克斯·库兹涅佐夫的《防御性数据库编程》一书。
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i INT = 1 ;
INSERT INTO dbo.Numbers (Number)
VALUES (1);
WHILE @i < 524289 --1048576
BEGIN;
INSERT INTO dbo.Numbers (Number)
SELECT Number + @i
FROM dbo.Numbers;
SET @i = @i * 2 ;
END
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
Method 5, taken from Arrays and Lists in SQL Server 2005 and Beyond article by Erland Sommarskog
方法5，从SQL Server 2005的数组和列表中提取，并在Erland Sommarskog的文章之外。
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
WITH digits (d) AS (
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 UNION ALL
SELECT 0)
INSERT INTO Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --999999 rows
Summary:
Among those 5 methods, method 3 seems to be the fastest.
总结:在这5种方法中，方法3似乎是最快的。
#9
1
Here is a short and fast in-memory solution that I came up with utilizing the Table Valued Constructors introduced in SQL Server 2008:
这里是一个简短的、快速的内存解决方案，我使用了SQL Server 2008中引入的表值构造函数来解决这个问题:
--1,000,000 rows. Either add/remove CROSS JOINs, or use TOP clause to modify this
;WITH v AS (SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) v(z))
SELECT N FROM (SELECT ROW_NUMBER() OVER (ORDER BY v1.z)-1 N FROM v v1
CROSS JOIN v v2 CROSS JOIN v v3 CROSS JOIN v v4 CROSS JOIN v v5 CROSS JOIN v v6) Nums
Note that this could be quickly calculated on the fly, or (even better) stored in a permanent table (just add an INTO clause after the SELECT N segment) with a primary key on the N field for improved efficiency.
注意，这可以快速计算，或者(甚至更好)存储在一个永久表中(仅在SELECT N段后添加一个INTO子句)，在N字段中使用主键，以提高效率。
#10
1
I know this thread is old and answered, but there is a way to squeeze a little extra performance out of Method 7:
我知道这条线是旧的，并且回答了，但是有一种方法可以从方法7中挤出一点额外的性能:
Instead of this (essentially method 7 but with some ease of use polish):
而不是这个(实际上是方法7，但是使用了一些简单的波兰语):
DECLARE @BIT AS BIT = 0
IF OBJECT_ID('tempdb..#TALLY') IS NOT NULL
DROP TABLE #TALLY
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #TALLY
FROM sys.objects s1 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 --use sys.co
ALTER TABLE #TALLY ADD PRIMARY KEY(Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
Try this:
试试这个:
DECLARE @BIT AS BIT = 0
IF OBJECT_ID('tempdb..#TALLY') IS NOT NULL
DROP TABLE #TALLY
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #TALLY
FROM (SELECT @BIT [X] UNION ALL SELECT @BIT) [T2]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T4]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T8]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T16]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T32]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T64]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T128]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T256]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T512]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T1024]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T2048]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T4096]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T8192]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T16384]
ALTER TABLE #TALLY ADD PRIMARY KEY(Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
On my server this takes ~10 ms as opposed to the ~16-20 ms when selecting from sys.objects. It also has the added benefit of not being dependent on how many objects are in sys.objects. While it's pretty safe, it's technically a dependency and the other one goes faster anyway. I think the speed boost is down to using BITs if you change:
在我的服务器上，当从sys.objects中选择时，这需要10毫秒，而不是~16-20毫秒。它还有一个额外的好处，那就是不依赖于系统中有多少对象。虽然它很安全，但从技术上讲，它是一种依赖，而另一种则运行得更快。我认为，如果你改变了，速度就会下降。
DECLARE @BIT AS BIT = 0
to:
:
DECLARE @BIT AS BIGINT = 0
It adds ~8-10 ms to the total time on my server. That said, when you scale up to 1,000,000 records BIT vs BIGINT doesn't appreciably affect my query anymore, but it still runs around ~680ms vs ~730ms from sys.objects.
它在我的服务器上添加了8-10毫秒的总时间。也就是说，当你达到了1,000,000个记录位，而BIGINT对我的查询不再有明显的影响，但是它仍然运行在大约680ms vs ~730ms的sys.objects中。
#1
111
here are some code examples taken from the web and from answers to this question.
下面是一些从web上获取的代码示例，以及这个问题的答案。
For Each Method, I have modified the original code so each use the same table and column: NumbersTest and Number, with 10,000 rows or as close to that as possible. Also, I have provided links to the place of origin.
对于每个方法，我都修改了原始代码，因此每个代码都使用相同的表和列:NumbersTest和Number，有10,000行或者尽可能接近这个。此外，我还提供了到原产地的链接。
METHOD 1 here is a very slow looping method from here
avg 13.01 seconds
ran 3 times removed highest, here are times in seconds: 12.42, 13.60
方法1这里是一个非常慢的循环方法从这里avg 13.01秒跑了3次最高，这里是几秒:12.42,13.60。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest(Number INT IDENTITY(1,1))
SET NOCOUNT ON
WHILE COALESCE(SCOPE_IDENTITY(), 0) < 100000
BEGIN
INSERT dbo.NumbersTest DEFAULT VALUES
END
SET NOCOUNT OFF
-- Add a primary key/clustered index to the numbers table
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE())/1000.0)+' seconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 2 here is a much faster looping one from here
avg 1.1658 seconds
ran 11 times removed highest, here are times in seconds: 1.117, 1.140, 1.203, 1.170, 1.173, 1.156, 1.203, 1.153, 1.173, 1.170
方法2，这里是一个更快速的循环，从这里，avg 1.1658秒跑了11次最高，这里是几秒:1.117,1.140,1.203,1.170,1.173,1.156,1.203,1.153,1.173,1.170。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number INT NOT NULL);
DECLARE @i INT;
SELECT @i = 1;
SET NOCOUNT ON
WHILE @i <= 10000
BEGIN
INSERT INTO dbo.NumbersTest(Number) VALUES (@i);
SELECT @i = @i + 1;
END;
SET NOCOUNT OFF
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE())/1000.0)+' seconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 3 Here is a single INSERT based on code from here
avg 488.6 milliseconds
ran 11 times removed highest, here are times in milliseconds: 686, 673, 623, 686,343,343,376,360,343,453
方法3这是一个基于代码的单一插入，avg488.6毫秒运行了11次最高，这里是几毫秒:686,673,623,6863343,376,360,343,453。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
;WITH Nums(Number) AS
(SELECT 1 AS Number
UNION ALL
SELECT Number+1 FROM Nums where Number<10000
)
insert into NumbersTest(Number)
select Number from Nums option(maxrecursion 10000)
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 4 here is a "semi-looping" method from here avg 348.3 milliseconds (it was hard to get good timing because of the "GO" in the middle of the code, any suggestions would be appreciated)
ran 11 times removed highest, here are times in milliseconds: 356, 360, 283, 346, 360, 376, 326, 373, 330, 373
4这是一个“semi-looping”方法从这里avg 348.3毫秒(很难得到“走”的好时机,因为中间的代码,任何建议,我们将不胜感激)跑11次最高,时间以毫秒为单位:356,360,283,346,360,376,326,373,330,330
DROP TABLE NumbersTest
DROP TABLE #RunDate
CREATE TABLE #RunDate (RunDate datetime)
INSERT INTO #RunDate VALUES(GETDATE())
CREATE TABLE NumbersTest (Number int NOT NULL);
INSERT NumbersTest values (1);
GO --required
INSERT NumbersTest SELECT Number + (SELECT COUNT(*) FROM NumbersTest) FROM NumbersTest
GO 14 --will create 16384 total rows
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
SELECT CONVERT(varchar(20),datediff(ms,RunDate,GETDATE()))+' milliseconds' FROM #RunDate
SELECT COUNT(*) FROM NumbersTest
METHOD 5 here is a single INSERT from Philip Kelley's answer
avg 92.7 milliseconds
ran 11 times removed highest, here are times in milliseconds: 80, 96, 96, 93, 110, 110, 80, 76, 93, 93
方法5这是菲利普·凯利的一个简单的回答avg92.7毫秒，运行了11次最高，这里是几毫秒:80,96,96,93,110,110,80,76,93,93。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
--I removed Pass5, since I'm only populating the Numbers table to 10,000
Tally as (select row_number() over(order by C) as Number from Pass4)
INSERT NumbersTest
(Number)
SELECT Number
FROM Tally
WHERE Number <= 10000
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 6 here is a single INSERT from Mladen Prajdic answer
avg 82.3 milliseconds
ran 11 times removed highest, here are times in milliseconds: 80, 80, 93, 76, 93, 63, 93, 76, 93, 76
方法6这是一个从Mladen Prajdic回答avg 82.3毫秒的单一插入，运行11次最高，这里是几毫秒:80、80、93、76、93、63、93、76、93、76。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
CREATE TABLE NumbersTest (Number int not null)
INSERT INTO NumbersTest(Number)
SELECT TOP 10000 row_number() over(order by t1.number) as N
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number);
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
METHOD 7 here is a single INSERT based on the code from here
avg 56.3 milliseconds
ran 11 times removed highest, here are times in milliseconds: 63, 50, 63, 46, 60, 63, 63, 46, 63, 46
方法7这是一个基于代码的单一插入，avg 56.3毫秒运行11次最高，这里是几毫秒的时间:63,50,63,46,60,63,63,46,63,46。
DROP TABLE NumbersTest
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO NumbersTest
FROM sys.objects s1 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
ALTER TABLE NumbersTest ADD CONSTRAINT PK_NumbersTest PRIMARY KEY CLUSTERED (Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
SELECT COUNT(*) FROM NumbersTest
After looking at all these methods, I really like Method 7, which was the fastest and the code is fairly simple too.
在查看了所有这些方法之后，我非常喜欢方法7，它是最快的，而且代码也是相当简单的。
#2
37
i use this which is fast as hell:
我用这个快得像地狱:
insert into Numbers(N)
select top 1000000 row_number() over(order by t1.number) as N
from master..spt_values t1
cross join master..spt_values t2
#3
13
If you're just doing this in SQL Server Management Studio or sqlcmd, you can use the fact that the batch separator allows you to repeat the batch:
如果您只是在SQL Server Management Studio或sqlcmd中这样做，您可以使用批分隔符允许您重复该批处理:
CREATE TABLE Number (N INT IDENTITY(1,1) PRIMARY KEY NOT NULL);
GO
INSERT INTO Number DEFAULT VALUES;
GO 100000
This will insert 100000 records into the Numbers table.
这将在数字表中插入100000条记录。
It's slow. It compares to METHOD 1 in @KM.'s answer, which is the slowest of the examples. However, it's about as code light as it gets. You could speed it up somewhat by adding the primary key constraint after the insert batch.
它是缓慢的。它与@KM中的方法1比较。这是最慢的例子。然而，它是关于代码的光。通过在插入批处理之后添加主键约束，可以稍微加快速度。
#4
9
I start with the following template, which is derived from numerous printings of Itzik Ben-Gan's routine:
我从下面的模板开始，这个模板来自Itzik beno - gan的日常工作的大量打印:
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
select Number from Tally where Number <= 1000000
The "WHERE N<= 1000000" clause limits the output to 1 to 1 million, and can easily be adjusted to your desired range.
“WHERE N<= 1000000”子句将输出限制在1到100万，并且可以很容易地调整到您想要的范围。
Since this is a WITH clause, it can be worked into an INSERT... SELECT... like so:
由于这是一个WITH子句，它可以被工作到插入…选择……像这样:
-- Sample use: create one million rows
CREATE TABLE dbo.Example (ExampleId int not null)
DECLARE @RowsToCreate int
SET @RowsToCreate = 1000000
-- "Table of numbers" data generator, as per Itzik Ben-Gan (from multiple sources)
;WITH
Pass0 as (select 1 as C union all select 1), --2 rows
Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),--4 rows
Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),--16 rows
Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),--256 rows
Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),--65536 rows
Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),--4,294,967,296 rows
Tally as (select row_number() over(order by C) as Number from Pass5)
INSERT Example (ExampleId)
select Number
from Tally
where Number <= @RowsToCreate
Indexing the table after it's built will be the fastest way to index it.
在表构建后对表进行索引将是索引它的最快方法。
Oh, and I'd refer to it as a "Tally" table. I think this is a common term, and you can find loads of tricks and examples by Googling it.
哦，我把它叫做“理货”表。我认为这是一个常见的术语，你可以通过谷歌搜索找到很多技巧和例子。
#5
3
For anyone looking for an Azure solution
对于任何寻找Azure解决方案的人来说。
SET NOCOUNT ON
CREATE TABLE Numbers (n bigint PRIMARY KEY)
GO
DECLARE @numbers table(number int);
WITH numbers(number) as (
SELECT 1 AS number
UNION all
SELECT number+1 FROM numbers WHERE number<10000
)
INSERT INTO @numbers(number)
SELECT number FROM numbers OPTION(maxrecursion 10000)
INSERT INTO Numbers(n) SELECT number FROM @numbers
Sourced from the sql azure team blog http://azure.microsoft.com/blog/2010/09/16/create-a-numbers-table-in-sql-azure/
来自于sql azure team博客http://azure.microsoft.com/blog/2010/09/16/crea-a -numbers-table-in-sql-azure/。
#6
2
I use numbers tables for primarily dummying up reports in BIRT without having to fiddle around with dynamic creation of recordsets.
我使用数字表主要是为了在BIRT中查找报告，而不需要摆弄记录集的动态创建。
I do the same with dates, having a table spanning from 10 years in the past to 10 years in the future (and hours of the day for more detailed reporting). It's a neat trick to be able to get values for all dates even if your 'real' data tables don't have data for them.
我对日期也做同样的事情，有一张从过去10年到未来10年的表格(以及更详细的报告的时间)。即使你的“真实”数据表没有数据，也可以为所有日期获取值。
I have a script which I use to create these, something like (this is from memory):
我有一个用来创建这些的脚本，比如(这是来自内存):
drop table numbers; commit;
create table numbers (n integer primary key); commit;
insert into numbers values (0); commit;
insert into numbers select n+1 from numbers; commit;
insert into numbers select n+2 from numbers; commit;
insert into numbers select n+4 from numbers; commit;
insert into numbers select n+8 from numbers; commit;
insert into numbers select n+16 from numbers; commit;
insert into numbers select n+32 from numbers; commit;
insert into numbers select n+64 from numbers; commit;
The number of rows doubles with each line so it doesn't take a lot to produce truly huge tables.
每一行的行数增加一倍，因此不需要花费大量的时间来生成真正的大表。
I'm not sure I agree with you that it's important to be created fast since you only create it once. The cost of that is amortized over all the accesses to it, rendering that time fairly insignificant.
我不确定我是否同意你的观点，因为你只创建一次，所以快速创建是很重要的。它的成本分摊到所有对它的访问上，使时间变得无关紧要。
#7
1
Some of the suggested methods are basing on system objects (for example on the 'sys.objects'). They are assuming these system objects contain enough records to generate our numbers.
一些建议的方法基于系统对象(例如，“sys.objects”)。他们假设这些系统对象包含足够的记录来生成我们的数字。
I would not base on anything which does not belong to my application and over which I do not have full control. For example: the content of these sys tables may change, the tables may not be valid anymore in new version of SQL etc.
我不会基于任何不属于我的应用程序和我没有完全控制的东西。例如:这些sys表的内容可能会发生变化，在新版本的SQL中，这些表可能不再有效。
As a solution, we can create our own table with records. We then use that one instead these system related objects (table with all numbers should be fine if we know the range in advance otherwise we could go for the one to do the cross join on).
作为解决方案，我们可以创建自己的表和记录。然后，我们使用那个系统相关的对象(如果我们事先知道了范围，那么所有的数据都应该没问题，否则我们就可以去做交叉连接了)。
The CTE based solution is working fine but it has limits related to the nested loops.
基于CTE的解决方案运行良好，但是它有与嵌套循环相关的限制。
#8
1
Here is a couple of extra methods:
Method 1
这里有一些额外的方法:方法1。
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i int = 1;
INSERT INTO dbo.Numbers (Number)
VALUES (1),(2);
WHILE 2*@i < 1048576
BEGIN
INSERT INTO dbo.Numbers (Number)
SELECT Number + 2*@i
FROM dbo.Numbers;
SET @i = @@ROWCOUNT;
END
GO
SELECT COUNT(*) FROM Numbers AS RowCownt --1048576 rows
Method 2
方法2
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i INT = 0;
INSERT INTO dbo.Numbers (Number)
VALUES (1);
WHILE @i <= 9
BEGIN
INSERT INTO dbo.Numbers (Number)
SELECT N.Number + POWER(4, @i) * D.Digit
FROM dbo.Numbers AS N
CROSS JOIN (VALUES(1),(2),(3)) AS D(Digit)
ORDER BY D.Digit, N.Number
SET @i = @i + 1;
END
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
Method 3
方法3
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int identity NOT NULL PRIMARY KEY, T bit NULL);
WITH
T1(T) AS (SELECT T FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS T(T)) --10 rows
,T2(T) AS (SELECT A.T FROM T1 AS A CROSS JOIN T1 AS B CROSS JOIN T1 AS C) --1,000 rows
,T3(T) AS (SELECT A.T FROM T2 AS A CROSS JOIN T2 AS B CROSS JOIN T2 AS C) --1,000,000,000 rows
INSERT INTO dbo.Numbers(T)
SELECT TOP (1048576) NULL
FROM T3;
ALTER TABLE Numbers
DROP COLUMN T;
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
Method 4, taken from Defensive Database Programming book by Alex Kuznetsov
方法4，取自亚历克斯·库兹涅佐夫的《防御性数据库编程》一书。
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
DECLARE @i INT = 1 ;
INSERT INTO dbo.Numbers (Number)
VALUES (1);
WHILE @i < 524289 --1048576
BEGIN;
INSERT INTO dbo.Numbers (Number)
SELECT Number + @i
FROM dbo.Numbers;
SET @i = @i * 2 ;
END
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --1048576 rows
Method 5, taken from Arrays and Lists in SQL Server 2005 and Beyond article by Erland Sommarskog
方法5，从SQL Server 2005的数组和列表中提取，并在Erland Sommarskog的文章之外。
IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL
DROP TABLE dbo.Numbers
GO
CREATE TABLE Numbers (Number int NOT NULL PRIMARY KEY);
GO
WITH digits (d) AS (
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 UNION ALL
SELECT 0)
INSERT INTO Numbers (Number)
SELECT Number
FROM (SELECT i.d + ii.d * 10 + iii.d * 100 + iv.d * 1000 +
v.d * 10000 + vi.d * 100000 AS Number
FROM digits i
CROSS JOIN digits ii
CROSS JOIN digits iii
CROSS JOIN digits iv
CROSS JOIN digits v
CROSS JOIN digits vi) AS Numbers
WHERE Number > 0
GO
SELECT COUNT(*) FROM dbo.Numbers AS RowCownt --999999 rows
Summary:
Among those 5 methods, method 3 seems to be the fastest.
总结:在这5种方法中，方法3似乎是最快的。
#9
1
Here is a short and fast in-memory solution that I came up with utilizing the Table Valued Constructors introduced in SQL Server 2008:
这里是一个简短的、快速的内存解决方案，我使用了SQL Server 2008中引入的表值构造函数来解决这个问题:
--1,000,000 rows. Either add/remove CROSS JOINs, or use TOP clause to modify this
;WITH v AS (SELECT * FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) v(z))
SELECT N FROM (SELECT ROW_NUMBER() OVER (ORDER BY v1.z)-1 N FROM v v1
CROSS JOIN v v2 CROSS JOIN v v3 CROSS JOIN v v4 CROSS JOIN v v5 CROSS JOIN v v6) Nums
Note that this could be quickly calculated on the fly, or (even better) stored in a permanent table (just add an INTO clause after the SELECT N segment) with a primary key on the N field for improved efficiency.
注意，这可以快速计算，或者(甚至更好)存储在一个永久表中(仅在SELECT N段后添加一个INTO子句)，在N字段中使用主键，以提高效率。
#10
1
I know this thread is old and answered, but there is a way to squeeze a little extra performance out of Method 7:
我知道这条线是旧的，并且回答了，但是有一种方法可以从方法7中挤出一点额外的性能:
Instead of this (essentially method 7 but with some ease of use polish):
而不是这个(实际上是方法7，但是使用了一些简单的波兰语):
DECLARE @BIT AS BIT = 0
IF OBJECT_ID('tempdb..#TALLY') IS NOT NULL
DROP TABLE #TALLY
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #TALLY
FROM sys.objects s1 --use sys.columns if you don't get enough rows returned to generate all the numbers you need
CROSS JOIN sys.objects s2 --use sys.co
ALTER TABLE #TALLY ADD PRIMARY KEY(Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
Try this:
试试这个:
DECLARE @BIT AS BIT = 0
IF OBJECT_ID('tempdb..#TALLY') IS NOT NULL
DROP TABLE #TALLY
DECLARE @RunDate datetime
SET @RunDate=GETDATE()
SELECT TOP 10000 IDENTITY(int,1,1) AS Number
INTO #TALLY
FROM (SELECT @BIT [X] UNION ALL SELECT @BIT) [T2]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T4]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T8]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T16]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T32]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T64]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T128]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T256]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T512]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T1024]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T2048]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T4096]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T8192]
CROSS JOIN (SELECT @BIT [X] UNION ALL SELECT @BIT) [T16384]
ALTER TABLE #TALLY ADD PRIMARY KEY(Number)
PRINT CONVERT(varchar(20),datediff(ms,@RunDate,GETDATE()))+' milliseconds'
On my server this takes ~10 ms as opposed to the ~16-20 ms when selecting from sys.objects. It also has the added benefit of not being dependent on how many objects are in sys.objects. While it's pretty safe, it's technically a dependency and the other one goes faster anyway. I think the speed boost is down to using BITs if you change:
在我的服务器上，当从sys.objects中选择时，这需要10毫秒，而不是~16-20毫秒。它还有一个额外的好处，那就是不依赖于系统中有多少对象。虽然它很安全，但从技术上讲，它是一种依赖，而另一种则运行得更快。我认为，如果你改变了，速度就会下降。
DECLARE @BIT AS BIT = 0
to:
:
DECLARE @BIT AS BIGINT = 0
It adds ~8-10 ms to the total time on my server. That said, when you scale up to 1,000,000 records BIT vs BIGINT doesn't appreciably affect my query anymore, but it still runs around ~680ms vs ~730ms from sys.objects.
它在我的服务器上添加了8-10毫秒的总时间。也就是说，当你达到了1,000,000个记录位，而BIGINT对我的查询不再有明显的影响，但是它仍然运行在大约680ms vs ~730ms的sys.objects中。