T-SQL 產生測試資料完整技巧：WHILE 迴圈、RAND() 進階用法、種子控制與 DEFAULT 值批次插入

場景一：簡單固定值批次插入 (利用 DEFAULT)

CREATE TABLE xyz (
    col1 int PRIMARY KEY IDENTITY(1,1),
    col2 int NOT NULL DEFAULT 999,
    col3 char(10) NOT NULL DEFAULT 'ABCDEFGHIJK'
);

DECLARE @i int = 1;
WHILE @i <= 100
BEGIN
    INSERT xyz DEFAULT VALUES;  -- 全欄位用預設值
    SET @i += 1;
END

場景二：RAND() 產生隨機測試資料

常用隨機模式

關鍵：指定種子確保可重現

-- 使用 @@SPID + CPU + Physical_IO 作為動態種子
DECLARE @seed int;
SELECT @seed = RAND(@@SPID + cpu + physical_io)
FROM master..sysprocesses WHERE spid = @@SPID;

同一種子下 RAND() 序列固定，利於測試驗證與效能基準比較。

場景三：千筆隨機資料完整範例 (兩種寫法)

寫法 A：變數迴圈 + 明確 INSERT

CREATE TABLE random_data (COL1 int PRIMARY KEY, col2 int, col3 char(15));
DECLARE @counter int=0, @col2 int, @col3 char(15);
SELECT @counter=0, @col2=RAND(@@SPID+cpu+physical_io) FROM master..sysprocesses WHERE spid=@@SPID;

WHILE @counter < 1000
BEGIN
    SET @counter += 10;  -- 遞增 10
    SET @col2 = CASE WHEN CONVERT(int,RAND()*100)%2=1 
                     THEN -CONVERT(int,RAND()*100000)%10000 
                     ELSE CONVERT(int,RAND()*100000)%10000 END;
    SET @col3 = CHAR((CONVERT(int,RAND()*1000)%26)+65) 
              + CHAR((CONVERT(int,RAND()*1000)%26)+65)
              + CHAR((CONVERT(int,RAND()*1000)%26)+65)
              + CHAR((CONVERT(int,RAND()*1000)%26)+65)
              + REPLICATE(CHAR((CONVERT(int,RAND()*1000)%26)+65), 11);
    INSERT random_data VALUES (@counter, @col2, @col3);
END

寫法 B：IDENTITY + DEFAULT VALUES (更簡潔)

CREATE TABLE random_data (
    COL1 int PRIMARY KEY IDENTITY(10,10) NOT NULL,
    col2 int NOT NULL DEFAULT CASE ... END,  -- 同上
    col3 char(15) NOT NULL DEFAULT ...       -- 同上
);
DECLARE @c int = 1;
WHILE @c < 1000 BEGIN INSERT random_data DEFAULT VALUES; SET @c+=1; END

進階技巧：
• 大量資料 (百萬級) 改用 CROSS JOIN 數字表 或 INSERT ... SELECT 避免 WHILE 迴圈 RBAR 效能問題。
• 需符合特定分佈 (常態、均勻、泊松) 可引入 CLR 函數或外部產生後 BULK INSERT。
• NEWID() 產生 GUID 亂序，常用於隨機抽樣：ORDER BY NEWID()。