萬盛學電腦網

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

SQL Server 快速產生測試資料的多種方法

方法一:系統函數 + 遞歸 CTE (無需建表)

-- 產生 1~100 號碼
WITH Nums(n) AS (
    SELECT 1 UNION ALL SELECT n+1 FROM Nums WHERE n < 100
)
SELECT n, 
       'User_' + RIGHT('000'+CAST(n AS VARCHAR(4)),4) AS UserName,
       DATEADD(DAY, -ABS(CHECKSUM(NEWID()))%365, GETDATE()) AS RegDate,
       ABS(CHECKSUM(NEWID()))%100000/100.0 AS Balance
FROM Nums
OPTION (MAXRECURSION 0);

方法二:spt_values 系統表 (舊版相容)

SELECT TOP 1000
    ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id,
    'Item_' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS VARCHAR(10)) AS Name,
    DATEADD(MINUTE, -ABS(CHECKSUM(NEWID()))%525600, GETDATE()) AS CreateTime
FROM master..spt_values a CROSS JOIN master..spt_values b;

方法三:TABLESAMPLE 快速抽樣現有大表

-- 近似 10% 隨機樣本 (頁面層級,非精確行數)
SELECT * FROM BigTable TABLESAMPLE (10 PERCENT) REPEATABLE (12345);

方法四:第三方工具 / 開源腳本

  • Redgate SQL Data Generator:視覺化、支援外鍵關聯、資料遮罩、CI/CD 整合。
  • dbForge Data Generator:類似功能,價格較親民。
  • tSQLt / dbatools (PowerShell):單元測試框架內建測試資料建立函數。
  • Mockaroo (線上):網頁產生 CSV/JSON/SQL,免費額度 1000 行/次。

方法五:資料遮罩複製生產資料 (GDPR/隱私合規)

-- 簡易遮罩範例
SELECT 
    Id,
    'User_' + CAST(Id AS VARCHAR(10)) AS UserName,  -- 替換真實姓名
    DATEADD(DAY, ABS(CHECKSUM(NEWID()))%30, DOB) AS DOB, -- 日期隨機偏移
    CASE WHEN Salary > 0 THEN Salary * (0.9 + RAND()*0.2) ELSE 0 END AS Salary -- 薪資 ±10%
INTO Dev_UserTable
FROM Prod_UserTable;

大量插入效能技巧

  1. 使用 BULK INSERT / BCP / SqlBulkCopy (C#) 而非逐行 INSERT。
  2. 暫時移除索引/約束/Trigger,匯入後重建。
  3. 設定資料庫 SIMPLE 復原模式或 BULK_LOGGED,減少記錄檔。
  4. 批次大小 1000~5000,配合 TABLOCK 提示。
copyright © 萬盛學電腦網 all rights reserved