在SQL Server上优化号码表创建? [英] Optimizing Numbers Table Creation on SQL Server?

问题描述

运行SQL Server Express2008.我为某些实用程序功能创建了一个数字表".由于表填充是自动化构建的一部分，因此每次部署事物时，这花费了过多的时间.

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的辅助表数字 杰夫·摩登(Jeff Moden).这是最快的

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屋！