SQL Server 2005 T-SQL 產生百萬筆測試資料的高效能寫法

背景：為何需要快速產生大量測試資料？

開發與效能測試常需建立百萬筆以上的數字序列表。傳統 WHILE 迴圈逐筆 INSERT 效能極差，SQL Server 2005 引入的 CTE（Common Table Expression）與視窗函數提供了極佳的集合式解法。

方案一：指數級倍增法（集合式迴圈）

CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
GO
DECLARE @max INT = 1000000, @rc INT = 1;
INSERT INTO dbo.Nums VALUES (1);
WHILE @rc * 2 <= @max
BEGIN
    INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
    SET @rc = @rc * 2;
END
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

原理：每輪將現有資料筆數翻倍（1→2→4→8…），僅需 log2(N) 輪即可完成，大幅減少交易日誌寫入與鎖定爭用。

方案二：遞迴 CTE（基礎版）

DECLARE @n BIGINT = 1000000;
WITH Nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Nums WHERE n < @n
)
SELECT n FROM Nums
OPTION (MAXRECURSION 0); -- 移除預設 100 層遞迴限制

雖語法簡潔，但逐行遞迴仍屬 RBAR（Row-By-Agonizing-Row），效能不如集合式寫法。

方案三：笛卡兒積 + ROW_NUMBER（高效能 CTE）

DECLARE @n BIGINT = 1000000;
WITH Base AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Base WHERE n < CEILING(SQRT(@n))
),
Expand AS (
    SELECT 1 AS c FROM Base AS B1, Base AS B2 -- 笛卡兒積產生 ~N 列
),
Nums AS (
    SELECT ROW_NUMBER() OVER (ORDER BY c) AS n FROM Expand
)
SELECT n FROM Nums WHERE n <= @n
OPTION (MAXRECURSION 0);

亮點：Base 只產生 √N 列，經笛卡兒積擴充至 N 列，再由 ROW_NUMBER 一次編號，極大降低遞迴深度與 CPU 開銷。

方案四：常數摺疊法（極致效能，適用於內嵌函數）

CREATE FUNCTION dbo.fn_nums(@n BIGINT) RETURNS TABLE
AS RETURN
WITH
    L0 AS (SELECT 1 AS c UNION ALL SELECT 1),           -- 2 列
    L1 AS (SELECT 1 AS c FROM L0 A, L0 B),              -- 4 列
    L2 AS (SELECT 1 AS c FROM L1 A, L1 B),              -- 16 列
    L3 AS (SELECT 1 AS c FROM L2 A, L2 B),              -- 256 列
    L4 AS (SELECT 1 AS c FROM L3 A, L3 B),              -- 65,536 列
    L5 AS (SELECT 1 AS c FROM L4 A, L4 B),              -- 4,294,967,296 列
    Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY c) AS n FROM L5)
SELECT n FROM Nums WHERE n <= @n;
GO

無任何遞迴，純編譯期常數摺疊，執行計畫極簡，**效能最強**，適合高頻呼叫的內嵌資料表值函數。

效能比較摘要

實務上建議：百萬級以上採用方案三或四，兼顧可讀性與極致效能。