您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

T-SQL 快速生成 50 萬筆測試資料：效能測試專用腳本

編輯：Oracle 資料庫基礎

場景動機

資料庫效能測試、索引驗證、分頁查詢壓測均需大量模擬資料。手工撰寫 T-SQL 腳本，單次執行即可生成百萬級測試表，無需外部工具。

一、建表結構（含擴展屬性文檔）

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID('[dbo].[LargeTable]') AND OBJECTPROPERTY(id, 'IsUserTable') = 1)
    DROP TABLE [dbo].[LargeTable];
GO

CREATE TABLE [dbo].[LargeTable] (
    [ID]       INT IDENTITY(1,1) NOT NULL,
    [Title]    NVARCHAR(100) COLLATE Chinese_PRC_CI_AS NULL,
    [Content]  NTEXT COLLATE Chinese_PRC_CI_AS NULL,
    [PublicTime] DATETIME NULL,
    [Author]   NVARCHAR(10) COLLATE Chinese_PRC_CI_AS NULL,
    [IsTop]    TINYINT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
GO

ALTER TABLE [dbo].[LargeTable] ADD CONSTRAINT [PK_LargeTable] PRIMARY KEY CLUSTERED ([ID]) ON [PRIMARY];
GO
ALTER TABLE [dbo].[LargeTable] ADD CONSTRAINT [DF_LargeTable_IsTop] DEFAULT (0) FOR [IsTop];
GO

-- 擴展屬性：自動生成資料字典
EXEC sp_addextendedproperty 'MS_Description', N'作者', 'user', 'dbo', 'table', 'LargeTable', 'column', 'Author';
EXEC sp_addextendedproperty 'MS_Description', N'內容', 'user', 'dbo', 'table', 'LargeTable', 'column', 'Content';
EXEC sp_addextendedproperty 'MS_Description', N'文章表，包含50萬條記錄', 'user', 'dbo', 'table', 'LargeTable', 'column', 'ID';
EXEC sp_addextendedproperty 'MS_Description', N'是否置頂 0.不置頂 1.置頂', 'user', 'dbo', 'table', 'LargeTable', 'column', 'IsTop';
EXEC sp_addextendedproperty 'MS_Description', N'發布時間', 'user', 'dbo', 'table', 'LargeTable', 'column', 'PublicTime';
EXEC sp_addextendedproperty 'MS_Description', N'文章標題', 'user', 'dbo', 'table', 'LargeTable', 'column', 'Title';
GO

二、資料生成邏輯（WHILE 迴圈 + RAND 隨機化）

DECLARE @i INT = 1, @count INT = 500000;
DECLARE @title NVARCHAR(100), @content NVARCHAR(100), @publictime DATETIME, @author NVARCHAR(10), @istop TINYINT;
DECLARE @rand_m TINYINT, @rand_d TINYINT, @rand_h TINYINT, @rand_mi TINYINT, @rand_s TINYINT;
DECLARE @str VARCHAR(30);

PRINT '開始執行時間：' + CAST(GETDATE() AS VARCHAR);

WHILE @i <= @count
BEGIN
    -- 隨機日期 2006 年內
    SET @rand_m = RAND(@i) * 12;
    SET @rand_d = RAND(@i) * 28;
    SET @rand_h = RAND(@i) * 24;
    SET @rand_mi = RAND(@i) * 60;
    SET @rand_s = RAND(@i) * 60;
    SET @str = '2006-' + CAST(@rand_m AS VARCHAR) + '-' + CAST(@rand_d AS VARCHAR) + ' ' 
             + CAST(@rand_h AS VARCHAR) + ':' + CAST(@rand_mi AS VARCHAR) + ':' + CAST(@rand_s AS VARCHAR);

    SET @title = N'文章標題' + CAST(@i AS VARCHAR);
    SET @content = N'文章內容' + CAST(@i AS VARCHAR);
    SET @publictime = CONVERT(DATETIME, @str, 120);
    SET @author = N'作者' + CAST(@i AS VARCHAR);
    SET @istop = CASE WHEN @i % 10000 = 0 THEN 1 ELSE 0 END;  -- 每 1 萬筆一條置頂

    INSERT INTO LargeTable (Title, Content, PublicTime, Author, IsTop)
    VALUES (@title, @content, @publictime, @author, @istop);

    SET @i = @i + 1;
END

PRINT '執行完畢時間：' + CAST(GETDATE() AS VARCHAR);

三、效能優化建議

批次提交：每 1 萬筆 COMMIT 或 CHECKPOINT，避免記錄檔暴漲
最小記錄：搭配 BULK_LOGGED 復原模式或 TABLOCK 提示
並行化：劃分 ID 範圍，多 Session 並行插入不同分區
身份值預熱：DBCC CHECKIDENT('LargeTable', RESEED, 0) 避免身份值碎片

四、驗證查詢

SELECT COUNT(*) AS TotalRows, 
       SUM(CASE WHEN IsTop = 1 THEN 1 ELSE 0 END) AS TopCount,
       MIN(PublicTime) AS MinDate, MAX(PublicTime) AS MaxDate
FROM LargeTable;