I have always found myself in need to generate numbers in a sequence. But as a DBA, I did not want to do it procedurally with a ‘WHILE loop with increment’. So I wrote this cool script that uses receurice CTEs. Hope it is helpful to you as it’s been for me.

DECLARE @n INT = 25

;WITH SeqNumbers (Num)
AS
(
SELECT 1 AS [Num]
UNION ALL
SELECT Num + 1 AS [Num]
FROM SeqNumbers
WHERE Num < @n
)
SELECT * FROM SeqNumbers
GO

See you next time with more tips,

_SqlTimes

NOTE: Sql Server, by default, allows only 100 recursions. But configurable.