优化 SQL Server 上的数字表创建? [英] Optimizing Numbers Table Creation on SQL Server?

问题描述

运行 SQL Server Express 2008.我为一些实用程序函数创建了一个数字表".由于表填充是自动构建的一部分，因此每次部署事物时都会花费大量时间.

Running SQL Server Express 2008. I created a "numbers table" for some utility functions. Since the table population is part of an automated build, this is taking an inordinate amount of time each time the thing is deployed.

冒着过度优化"的风险，任何人都可以评论我如何让这一切尽快完成吗?也许在使用索引填充因子或创建 PK 时?

At the risk of "over-optimizing", can anyone comment on how I can make this go as fast as possible? Maybe playing with the index fill factor or when the PK is created?

IF EXISTS (SELECT *  FROM dbo.sysobjects 
WHERE id = OBJECT_ID(N'Numbers') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
drop TABLE [Numbers]
end

CREATE TABLE [Numbers]
(
      [Number] [int]
    , CONSTRAINT [Index_Numbers] PRIMARY KEY CLUSTERED
        (
            [number] ASC
        ) ON [PRIMARY]
) 
ON [PRIMARY]
Declare @cnt int
Select @cnt=0
SET NOCOUNT ON
while (@cnt<10000)
BEGIN
INSERT INTO NUMBERS(NUMBER) SELECT @cnt
SELECT @cnt=@cnt+1

end

推荐答案

SQL，辅助表数字通过杰夫摩登.这个是最快的

SQL, Auxiliary table of numbers by Jeff Moden. This one was the fastest

--===== 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

这篇关于优化 SQL Server 上的数字表创建?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！