測試或示範資料時,常需要快速插入大量連續整數。利用遞迴 CTE(Common Table Expression)的指數增長特性,可在極短時間產生百萬級數列。
WITH
l0 AS (SELECT 1 AS C UNION ALL SELECT 1), -- 2^1 = 2
l1 AS (SELECT 1 AS C FROM l0 A, l0 B), -- 2^2 = 4
l2 AS (SELECT 1 AS C FROM l1 A, l1 B), -- 2^4 = 16
l3 AS (SELECT 1 AS C FROM l2 A, l2 B), -- 2^8 = 256
l4 AS (SELECT 1 AS C FROM l3 A, l3 B), -- 2^16 = 65,536
l5 AS (SELECT 1 AS C FROM l4 A, l4 B) -- 2^32 ≈ 42 億
SELECT ROW_NUMBER() OVER (ORDER BY C) AS n
INTO Nums
FROM l5;
CROSS JOIN(舊式逗號語法)將上一層列數平方l5 理論可產生 42 億列,實際受限於 MAXRECURSION 與資源ROW_NUMBER() 為虛擬列賦予連續編號INTO Nums 直接建立實體資料表存放結果-- 產生 1 到 100,000 的連續數字
WITH N(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM N WHERE n < 100000
)
SELECT n INTO TestNumbers FROM N
OPTION (MAXRECURSION 0); -- 移除預設 100 層遞迴限制
此技巧無需迴圈、無需臨時表,純集合運算,是 T-SQL 開發者的利器。