¿Cuál es la mejor manera de crear y completar una tabla de números?

He visto muchas formas diferentes de crear y poblar una tabla de números. Sin embargo, ¿cuál es la mejor manera de crear y poblar uno? Con “lo mejor” se define de mayor a menor importancia:

  • Tabla creada con indexación óptima
  • filas generadas más rápidas
  • código simple usado para crear y poblar

Si no sabes qué es una tabla de números, mira aquí: ¿Por qué debería considerar usar una tabla de números auxiliares?

aquí hay algunos ejemplos de códigos tomados de la web y de respuestas a esta pregunta.

Para cada Método, he modificado el código original para que cada uno use la misma tabla y columna: NumbersTest y Number, con 10,000 filas o tan cerca como sea posible. Además, he proporcionado enlaces al lugar de origen.

MÉTODO 1 aquí es un método de bucle muy lento desde aquí
promedio 13.01 segundos
corrió 3 veces más alto, aquí están los tiempos en segundos: 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 

MÉTODO 2 aquí es un bucle mucho más rápido de aquí
promedio 1.1658 segundos
corrió 11 veces más alto, aquí están los tiempos en segundos: 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 

MÉTODO 3 Aquí hay un INSERT solo basado en el código de aquí
promedio 488,6 milisegundos
corrió 11 veces más alto, aquí hay veces en milisegundos: 686, 673, 623, 686,343,343,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 aquí es un método "semi-looping" desde aquí promedia 348.3 milisegundos (fue difícil obtener un buen timing debido al "GO" en el medio del código, cualquier sugerencia sería apreciada)
ejecutó 11 veces la eliminación más alta, aquí hay momentos en milisegundos: 356, 360, 283, 346, 360, 376, 326, 373, 330, 373

 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 

MÉTODO 5 aquí es un único INSERT de la respuesta de Philip Kelley
promedio de 92.7 milisegundos
corrió 11 veces más alto, aquí están los tiempos en milisegundos: 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 

MÉTODO 6 aquí es un solo INSERT de la respuesta de Mladen Prajdic
promedio 82.3 milisegundos
ejecutó 11 veces la eliminación más alta, aquí hay veces en milisegundos: 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 

MÉTODO 7 aquí es un INSERT solo basado en el código de aquí
promedio de 56.3 milisegundos
corrió 11 veces más alto, aquí hay veces en milisegundos: 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 

Después de ver todos estos métodos, realmente me gusta el Método 7, que fue el más rápido y el código es bastante simple también.

Yo uso esto que es tan rápido como el infierno:

 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 

Si solo hace esto en SQL Server Management Studio o sqlcmd, puede usar el hecho de que el separador de lotes le permite repetir el lote:

 CREATE TABLE Number (N INT IDENTITY(1,1) PRIMARY KEY NOT NULL); GO INSERT INTO Number DEFAULT VALUES; GO 100000 

Esto insertará 100000 registros en la tabla Numbers .

Es lento. Se compara con el MÉTODO 1 en la respuesta de @ KM., Que es el más lento de los ejemplos. Sin embargo, es casi tan claro como el código. Puede acelerarlo de alguna manera agregando la restricción de clave primaria después del lote de inserción.

Comienzo con la siguiente plantilla, que se deriva de numerosas impresiones de la rutina de Itzik Ben-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 

La cláusula "WHERE N <= 1000000" limita la salida a 1 a 1 millón, y puede ajustarse fácilmente a su rango deseado.

Como se trata de una cláusula WITH, se puede trabajar en un INSERT ... SELECT ... así:

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

La indexación de la tabla después de su construcción será la forma más rápida de indexarla.

Ah, y me referiría a él como una tabla de "Tally". Creo que este es un término común, y puedes encontrar montones de trucos y ejemplos buscando en Google.

Para cualquiera que busque una solución 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 

Procedente del blog del equipo sql azure http://azure.microsoft.com/blog/2010/09/16/create-a-numbers-table-in-sql-azure/

Utilizo tablas de números para principalmente generar informes en BIRT sin tener que jugar con la creación dinámica de conjuntos de registros.

Hago lo mismo con las fechas, con una tabla que abarca desde 10 años en el pasado hasta 10 años en el futuro (y horas del día para informes más detallados). Es un buen truco poder obtener valores para todas las fechas, incluso si sus tablas de datos “reales” no tienen datos para ellos.

Tengo un script que uso para crear estos, algo así como (esto es de memoria):

 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; 

El número de filas se duplica con cada línea, por lo que no se necesita mucho para producir tablas verdaderamente enormes.

No estoy seguro de estar de acuerdo contigo en que es importante crearlo rápido ya que solo lo creas una vez. El costo de eso se amortiza en todos los accesos, lo que hace que ese tiempo sea insignificante.

Aquí hay un par de métodos adicionales:
Método 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 

Método 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 

Método 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 AT FROM T1 AS A CROSS JOIN T1 AS B CROSS JOIN T1 AS C) --1,000 rows ,T3(T) AS (SELECT AT 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 

Método 4 , tomado del libro de Progtwigción Defensiva de la Base de Datos de Alex Kuznetsov

 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 

Método 5 , tomado de Arrays and Lists en SQL Server 2005 y artículo de Beyond de 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 id + ii.d * 10 + iii.d * 100 + iv.d * 1000 + vd * 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 

Resumen:
Entre esos 5 métodos, el método 3 parece ser el más rápido.

Algunos de los métodos sugeridos se basan en objetos del sistema (por ejemplo, en ‘sys.objects’). Suponen que estos objetos del sistema contienen suficientes registros para generar nuestros números.

No me basaría en nada que no pertenezca a mi aplicación y sobre el cual no tenga control total. Por ejemplo: el contenido de estas tablas sys puede cambiar, las tablas pueden no ser más válidas en la nueva versión de SQL, etc.

Como solución, podemos crear nuestra propia tabla con registros. Entonces usamos ese en lugar de estos objetos relacionados con el sistema (la tabla con todos los números debería estar bien si conocemos el rango por adelantado; de lo contrario, podríamos elegir el que haga la combinación cruzada).

La solución basada en CTE funciona bien, pero tiene límites relacionados con los bucles nesteds.

Aquí hay una solución corta y rápida en la memoria que se me ocurrió con la utilización de los constructores de tabla de valor introducidos en 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 

Tenga en cuenta que esto se puede calcular rápidamente sobre la marcha, o (incluso mejor) almacenado en una tabla permanente (simplemente agregue una cláusula INTO después del segmento SELECT N ) con una clave principal en el campo N para una mejor eficiencia.

Sé que este hilo es viejo y se ha respondido, pero hay una forma de obtener un poco más de rendimiento del Método 7:

En lugar de esto (esencialmente el método 7 pero con cierta facilidad de uso polaco):

 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' 

Prueba esto:

 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' 

En mi servidor esto toma ~ 10 ms en comparación con los ~ 16-20 ms cuando se selecciona desde sys.objects. También tiene el beneficio adicional de no depender de cuántos objetos hay en sys.objects. Si bien es bastante seguro, técnicamente es una dependencia y el otro va más rápido de todos modos. Creo que el aumento de velocidad se reduce a usar BIT si cambias:

 DECLARE @BIT AS BIT = 0 

a:

 DECLARE @BIT AS BIGINT = 0 

Agrega ~ 8-10 ms al tiempo total en mi servidor. Dicho eso, cuando escalas hasta 1,000,000 de registros, BIT vs BIGINT ya no afecta apreciablemente mi consulta, pero aún se ejecuta alrededor de ~ 680ms vs ~ 730ms desde sys.objects.