Crear una tabla permanente de números consecutivos

Una forma directa de disponer de una secuencia numérica es mediante la creación de una tabla física persistente. Aunque el uso de bucles genera más registros de transacción, este enfoque es aceptable si solo se ejecuta una vez:

IF OBJECT_ID('dbo.SequenceNumbers') IS NOT NULL 
    DROP TABLE dbo.SequenceNumbers;

CREATE TABLE dbo.SequenceNumbers (id INT NOT NULL PRIMARY KEY);

DECLARE @counter INT = 1;
WHILE @counter <= 1000
BEGIN
    INSERT INTO dbo.SequenceNumbers (id) VALUES (@counter);
    SET @counter += 1;
END;

SELECT * FROM dbo.SequenceNumbers;

Utilizar la vista del sistema master..spt_values

La tabla master..spt_values contiene una columna number que proporciona valores enteros desde 0 hasta 2047 cuando el tipo es 'P'. Es útil para generar secuencias pequeñas sin crear objetos adicionales:

SELECT number AS n
FROM master..spt_values
WHERE type = 'P' AND number > 0;

Generar secuencias con CTE recursiva

Una expresión de tabla común (CTE) recursiva permite construir una serie numérica de forma clara y compacta. Aunque no es la opción más rápida para grandes volúmenes, su legibilidad es ventajosa:

DECLARE @maxValue BIGINT = 100000;

WITH NumberSequence AS (
    SELECT 1 AS val
    UNION ALL
    SELECT val + 1
    FROM NumberSequence
    WHERE val < @maxValue
)
SELECT val AS n
FROM NumberSequence
OPTION (MAXRECURSION 0);

Construcción mediante cruzado de dígitos (0-9)

Este método utiliza una tabla derivada con los dígitos del 0 al 9 y realiza múltiples combinaciones cruzadas para formar unidades, decenas, centenas, etc. Escala bien y evita recursiones:

DECLARE @Digits TABLE(d INT);
INSERT INTO @Digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT D1.d + (D2.d * 10) + (D3.d * 100) + 1 AS n
FROM @Digits AS D1
CROSS JOIN @Digits AS D2
CROSS JOIN @Digits AS D3
ORDER BY n;

Este ejemplo genera números del 1 al 1000. Para rangos mayores, añadir más niveles de cruce.

Función con potencias de dos para alta eficiencia

Este enfoque aprovecha multiplicaciones sucesivas por cruzamiento para generar millones de filas rápidamente. Se basa en elevar 2 varias veces: (2²)⁵ = 4.294.967.296 combinaciones posibles. Luego se usa ROW_NUMBER() para obtener la secuencia final. Ideal para funciones en línea:

IF OBJECT_ID('dbo.GenerateNumbers') IS NOT NULL 
    DROP FUNCTION dbo.GenerateNumbers;
GO

CREATE FUNCTION dbo.GenerateNumbers(@inicio BIGINT, @fin BIGINT)
RETURNS TABLE
AS
RETURN
WITH 
    B0 AS (SELECT 1 AS x FROM (VALUES (1),(1)) AS V(x)),
    B1 AS (SELECT 1 AS x FROM B0 CROSS JOIN B0 AS b),
    B2 AS (SELECT 1 AS x FROM B1 CROSS JOIN B1 AS b),
    B3 AS (SELECT 1 AS x FROM B2 CROSS JOIN B2 AS b),
    B4 AS (SELECT 1 AS x FROM B3 CROSS JOIN B3 AS b),
    B5 AS (SELECT 1 AS x FROM B4 CROSS JOIN B4 AS b),
    Secuencia AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn FROM B5)
SELECT @inicio + rn - 1 AS numero
FROM Secuencia
ORDER BY rn
OFFSET 0 ROWS FETCH NEXT (@fin - @inicio + 1) ROWS ONLY;

Uso:

SELECT * FROM dbo.GenerateNumbers(1, 500);

Aplicaciones prácticas de tablas de números

Estas secuencias son útiles para generar series de fechas o analizar cadenas:

• Horas de un día:

SELECT DATEADD(HOUR, number, '2023-02-20') AS hora
FROM master..spt_values
WHERE type = 'P' AND number BETWEEN 0 AND 23;

• Meses desde una fecha base:

SELECT FORMAT(DATEADD(MONTH, number, '1994-01-01'), 'yyyy-MM') AS mes
FROM master..spt_values
WHERE type = 'P' AND number <= DATEDIFF(MONTH, '1994-01-01', GETDATE());

• Días entre dos fechas:

SELECT CAST(DATEADD(DAY, number, '2022-01-01') AS DATE) AS dia
FROM master..spt_values
WHERE type = 'P' AND number <= DATEDIFF(DAY, '2022-01-01', GETDATE());

• Caracteres comunes entre dos cadenas:

DECLARE @cadenaA VARCHAR(100) = 'amor en el pasado';
DECLARE @cadenaB VARCHAR(100) = 'memoria plena hoy';

SELECT DISTINCT SUBSTRING(@cadenaA, number, 1) AS caracter
FROM master..spt_values
WHERE type = 'P'
  AND number BETWEEN 1 AND LEN(@cadenaA)
  AND CHARINDEX(SUBSTRING(@cadenaA, number, 1), @cadenaB) > 0;