Каков наилучший способ создания и заполнения таблицы чисел?

Я использую это, что быстро, как ад:

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

Если вы просто делаете это в 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

это вставит 100000 записей в Numbers таблица.

это медленно. Он сравнивается с методом 1 в @KM.ответ, который является самым медленным из примеров. Тем не менее, это примерно как код света, как он получает. Вы можете несколько ускорить его, добавив ограничение первичного ключа после пакета вставки.

Я начинаю со следующего шаблона, который получен из многочисленных печатных изданий рутины Ицика Бен-Гана:

;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

предложение "WHERE N

Так как это предложение WITH, оно может быть обработано в INSERT... ВЫБИРАТЬ... вот так:

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

индексирование таблицы после ее построения будет самым быстрым способом индексировать ее.

О, и я обращусь к нему как к" Счетной " таблице. Я думаю, что это общий термин, и вы можете найти множество трюков и примеров, погуглив его.

для тех, кто ищет решение 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

источник из блога команды sql azure http://azure.microsoft.com/blog/2010/09/16/create-a-numbers-table-in-sql-azure/

Я использую таблицы чисел в первую очередь для создания отчетов в BIRT без необходимости возиться с динамическим созданием наборов записей.

Я делаю то же самое с датами, имея таблицу, охватывающую от 10 лет в прошлом до 10 лет в будущем (и часы дня для более подробной отчетности). Это аккуратный трюк, чтобы иметь возможность получить значения для все даты, даже если ваши "реальные" таблицы данных не имеют данных для них.

У меня есть скрипт, который я использую, чтобы создать эти, что-то вроде (это по памяти):

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;

количество строк удваивается с каждой строкой, поэтому для создания действительно огромных таблиц не требуется много времени.

Я не уверен, что согласен с вами, что это важно, чтобы быть создан быстро, так как вы только создать его один раз. Стоимость этого амортизируется по всем доступам к нему, что делает это время довольно незначительным.

вот несколько дополнительных методов:
Способ 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

Способ 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

Способ 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

метод 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

Метод 5, принятым от массивы и списки в SQL Server 2005 и за его пределами статья Ерланд Сайт 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

резюме:
Среди этих 5 методов, метод 3, кажется, самый быстрый.

некоторые из предложенных методов основаны на системных объектах (например, на 'sys.объекты.)' Они предполагают, что эти системные объекты содержат достаточно записей, чтобы генерировать наши номера.

Я бы не основывался ни на чем, что не относится к моему приложению и над которым у меня нет полного контроля. Например: содержание этих таблиц sys может измениться, таблицы могут быть недействительными больше в новой версии SQL и т. д.

Как решение, мы можем создать наши собственные таблица с записями. Затем мы используем этот один вместо этих связанных с системой объектов (таблица со всеми числами должна быть в порядке, если мы заранее знаем диапазон, иначе мы могли бы пойти на тот, чтобы сделать перекрестное соединение).

решение на основе CTE работает нормально, но оно имеет ограничения, связанные с вложенными циклами.

вот короткое и быстрое решение в памяти, которое я придумал с использованием Конструкторы С Табличным Значением введено в 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

обратите внимание, что это может быть быстро вычислено на лету, или (еще лучше) хранится в постоянной таблице (просто добавьте INTO п. После SELECT N сегмент) с первичным ключом на N поле для повышения эффективности.

Я знаю, что этот поток старая и ответил, но есть способ выжать немного больше из Способ 7:

вместо этого (по существу метод 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'

попробуйте это:

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'

на моем сервере это занимает ~10 мс в отличие от ~16-20 мс при выборе из sys.объекты. Он также имеет дополнительное преимущество, не зависящее от того, сколько объектов находится в sys.объекты. Хотя это довольно безопасно, это технически зависимость, а другой идет быстрее в любом случае. Я думаю, что повышение скорости сводится к использованию битов, если вы измените:

DECLARE @BIT AS BIT = 0

to:

DECLARE @BIT AS BIGINT = 0

Он добавляет ~8-10 МС к общему времени на моем сервере. Тем не менее, когда вы масштабируете до 1 000 000 записей бит vs BIGINT больше не влияет на мой запрос, но он все еще работает вокруг ~680 мс против ~730 МС из sys.объекты.