SQL 技巧：利用 CTE 產生百萬級整數序列表 (0~999,999)

核心技術：SQL Server 2005 CTE (Common Table Expression) 遞迴/交叉聯結

單一陳述式產生 1,000,000 列

WITH Digits AS (
    SELECT 0 AS Number 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
)
SELECT 
    (d5.Number * 100000) + (d4.Number * 10000) + (d3.Number * 1000)
  + (d2.Number * 100)    + (d1.Number * 10)     + d0.Number AS Number
FROM Digits AS d0
CROSS JOIN Digits AS d1
CROSS JOIN Digits AS d2
CROSS JOIN Digits AS d3
CROSS JOIN Digits AS d4
CROSS JOIN Digits AS d5
ORDER BY Number;  -- 0 到 999,999

原理解析

• Digits CTE 產生 0-9 十個數字 (10 列)。
• 六個 CROSS JOIN 形成 **10^6 = 1,000,000** 種排列組合。
• 位權運算將各位數字合成最終整數。
• 全記憶體運算，**無 I/O、極快** (通常 < 1 秒)。

SQL Server 2000 / 不支援 CTE 環境移植方案

將 CTE 拆為兩個**檢視表**：

-- 1. 基礎數字檢視表
CREATE VIEW dbo.Digits AS
SELECT 0 AS Number 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;

-- 2. 百萬數字檢視表
CREATE VIEW dbo.MillionNumbers AS
SELECT 
    (d5.Number * 100000) + (d4.Number * 10000) + (d3.Number * 1000)
  + (d2.Number * 100)    + (d1.Number * 10)     + d0.Number AS Number
FROM Digits d0 CROSS JOIN Digits d1 CROSS JOIN Digits d2
     CROSS JOIN Digits d3 CROSS JOIN Digits d4 CROSS JOIN Digits d5;

實戰應用：大量測試資料秒級生成

INSERT INTO MyTest (RecordId, RecordIndex)
SELECT NEWID(), Number FROM MillionNumbers;

**效能對比：** 集合式 INSERT ... SELECT 較迴圈 WHILE ... INSERT **快數十至數百倍**，並避免記錄檔膨脹 (可搭配 BULK LOGGED 恢復模式)。

CTE + 交叉聯結 是 SQL Server 產生連續序列、日期序列、測試資料的**標準高效模式**，無需迴圈、無臨時表、純集合運算。