萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 快速產生大量連續數字的 CTE 技巧

測試或示範資料時,常需要快速插入大量連續整數。利用遞迴 CTE(Common Table Expression)的指數增長特性,可在極短時間產生百萬級數列。

核心 SQL 腳本

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;

原理解析

  • 每一層 CTE 透過 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 層遞迴限制

效能提示

  • 大量資料建議分批次寫入,避免交易記錄暴增
  • 若僅需臨時數列,改用表變數或暫存表
  • 生產環境大量測試資料建議使用專用工具(如 SQL Data Generator)

此技巧無需迴圈、無需臨時表,純集合運算,是 T-SQL 開發者的利器。

copyright © 萬盛學電腦網 all rights reserved