[Code] Cómo generar N Filas Rápido Sql Server

Como siempre, una de las cosas que mas se necesita al crear un procedimiento es generar un número de determinado de filas para poderlas combinar con alguna otra tabla, ésta técnica presenta cómo crear una tabla que cuente de un número a otro, en incrementos de mánera eficiente

LA PRIMERA APROXIMACIÓN

declare @StartNumber int, @Nrows int, @increment int


set @StartNumber = 1
set @Nrows = 1000000
set @increment = 1


----
declare @q table (i int)
declare @index int

set @index = @StartNumber

while @index < @Nrows
Begin
insert into @q values(@index)
set @index = @index + @increment
End

select i from @q

En éste punto, se genera el resultado y tiene los parámetros que se podrían esperar: el Inicio, El número de filas y el Incremento, pero al ejecutarlo con un millón de filas, toma (en mi computador) 25 segundos, lo cual es muy lento, sin emabrgo ésta aproximación tendríaa el límite de registros del tipo de datos que usemos como parámetro, es decir, se puede llegar hasta el máximo que permite un entero, o el máximo que soporte un bigint, lo cual es bastante grande, pero mucho mas lento al hacerlo uno por uno. Ahora vamos a investigar otra alternativa que nos permitirá generar los registros mucho más rápido usando otras técnicas

SEGUNDA APROXIMACIÓN

declare @StartNumber int, @NumberOfRows int, @Increment int

set @StartNumber = 1
set @NumberOfRows = 1000000
set @Increment = 1

declare @Rows table (i int)

Declare @NumGen Table (Num int)
Declare @cnt int
Set @cnt = @StartNumber
While @cnt <= 100
Begin
Insert Into @NumGen values (@cnt)
Set @cnt = @cnt + @Increment
End
set @StartNumber = @StartNumber - @Increment

insert into @Rows
Select @StartNumber + RowNum From
(
Select Row_Number() Over (Order By N1.Num) As RowNum
From @NumGen N1, @NumGen N2, @NumGen N3, @NumGen N4, @NumGen N5, @NumGen N6, @NumGen N7, @NumGen N8, @NumGen N9, @NumGen N10) RowNums
Where RowNum <= @NumberOfRows

select * from @Rows

 

Lo que sucede es que estamos haciendo una generación inicial de una tabla hasta 100 normalmente (también porque para casos en donde la tabla a generar es mas corta) y luego multiplicando por conjuntos éste esfuerzo con el fin de no generar una a una las filas sino por bloques de a 100 filas y en éste caso 10 bloques de a 100 filas, al hacer ésta combinación aplicamos una combinación lo cual nos da un máximo de filas a generar de 100^10, lo cual suficiente en la mayoría de los casos.

 

Usando nuestra solución podemos generar la función con la firma:

 

alter function GenTable(@StartNumber int, @NumberOfRows int, @Increment int = 1)

 

Y podemos utilizarlo para crear un ejemplo: Imaginemos que queremos agregar hora a hora las siguientes 12 horas a las fechas de actividad de un cliente, cómo se podría hacer?

EJEMPLO DE USO

--Tabla que proviene de la Base de Datos simulada--
declare @fechas table (IdCliente int, Fecha datetime)

insert into @fechas values
(1, '2001-01-20 08:23:20'),
(2, '2001-02-24 18:23:20'),
(3, '2001-03-23 12:23:20')
--Fin Tabla que proviene de la Base de Datos simulada--

select * from @fechas

insert into @fechas
select IdCliente, DATEADD(hh,ids.i,fecha) as FechasHoras from @fechas
cross join (select i from dbo.GenTable(1,12,default)) Ids
order by FechasHoras

select * from @fechas
order by Fecha

 

Aplicamos cada registro generado a cada una de las filas (CrossJoin) que provienen de la tabla y luego usamos la función DateAdd para hacer el trabajo fila a fila, sin usar cursores, sin necesidad de recorrer la variable tipo tabla y como siempre favoreciendo las operacione de conjunto sobre las operaciones realizadas fila a fila

 Por supuesto exiiten otras alternattivas se puede generar usando CTE, la cuestión es la bandera MAXRECURSION, la cual limita el rango de acción de la solución planteada a cierto número de registos (depende de la implementación) Y claro la MAS recomendada y eficiente de todas que es usar CLR, especial para este tipo de casos Los invito a probar las diferentes alternativas y enviar sus comentarios sobre el tema

FREDDY LEANDRO ANGARITA CASTELLANOS
Sql Server MVP