SQL Server 快速產生 10,000 筆連續編號的集合式寫法

需求背景

測試、示範或填充樣本資料時，常需要快速產生大量連續編號。傳統 WHILE 迴圈效能低落，利用集合式運算 (Cross Join) 可在單一陳述式中高效完成。

核心邏輯：位數拆解與笛卡兒積

將 0~9999 拆解為四個位數：千位、百位、十位、個位。每個位數獨立產生 0~9 (乘以對應權重)，透過 CROSS JOIN 產生 10×10×10×10 = 10,000 種組合，最後相加得連續編號。

完整 T-SQL 程式碼

-- 記錄開始時間
PRINT CONVERT(VARCHAR(50), GETDATE(), 21)

-- 產生 1 ~ 10000 連續編號
SELECT id = a.id + b.id + c.id + d.id + 1
FROM (
    SELECT id = 0 UNION ALL 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
) a                                 -- 個位：0~9
CROSS JOIN (
    SELECT id = 0 UNION ALL SELECT 10 UNION ALL SELECT 20 UNION ALL SELECT 30 UNION ALL SELECT 40
    UNION ALL SELECT 50 UNION ALL SELECT 60 UNION ALL SELECT 70 UNION ALL SELECT 80 UNION ALL SELECT 90
) b                                 -- 十位：0~90
CROSS JOIN (
    SELECT id = 0 UNION ALL SELECT 100 UNION ALL SELECT 200 UNION ALL SELECT 300 UNION ALL SELECT 400
    UNION ALL SELECT 500 UNION ALL SELECT 600 UNION ALL SELECT 700 UNION ALL SELECT 800 UNION ALL SELECT 900
) c                                 -- 百位：0~900
CROSS JOIN (
    SELECT id = 0 UNION ALL SELECT 1000 UNION ALL SELECT 2000 UNION ALL SELECT 3000 UNION ALL SELECT 4000
    UNION ALL SELECT 5000 UNION ALL SELECT 6000 UNION ALL SELECT 7000 UNION ALL SELECT 8000 UNION ALL SELECT 9000
) d                                 -- 千位：0~9000
ORDER BY id

-- 記錄結束時間
PRINT CONVERT(VARCHAR(50), GETDATE(), 21)

執行效能對比

擴充應用：產生任意範圍與步長

-- 產生 5000 ~ 15000，步長 5
SELECT id = (a.id + b.id + c.id + d.id) * 5 + 5000
FROM (...) a CROSS JOIN (...) b CROSS JOIN (...) c CROSS JOIN (...) d
WHERE (a.id + b.id + c.id + d.id) * 5 + 5000 BETWEEN 5000 AND 15000
ORDER BY id

-- 產生日期序列 (近 30 天)
SELECT DATEADD(DAY, n - 1, '2024-01-01') AS dt
FROM (
    SELECT a.id + b.id + c.id + d.id + 1 AS n
    FROM (...) a CROSS JOIN (...) b CROSS JOIN (...) c CROSS JOIN (...) d
) x WHERE n <= 30

進階技巧：建立常駐數字表

若頻繁需要數字序列，建議建立常駐資料表：

CREATE TABLE dbo.Numbers (n INT PRIMARY KEY)
INSERT dbo.Numbers (n)
SELECT a.id + b.id + c.id + d.id + 1
FROM (...) a CROSS JOIN (...) b CROSS JOIN (...) c CROSS JOIN (...) d
-- 後續直接 JOIN Numbers 表即可，效能最佳