sql server - varias - ¿Cuál es la mejor manera de crear y completar una tabla de números?
funciones con valores de tabla sql server (10)
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 anidados.
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 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 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
Método 4 , tomado del libro de Programació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 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
Resumen:
Entre esos 5 métodos, el método 3 parece ser el más rápido.
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.
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/
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.
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.
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.
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
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 here
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 here
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 here
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 here 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 here
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.