高流量系統面對海量寫入時,單表容量與索引維護成本急劇上升。按日期動態建表(日分表)是常見應對策略:將資料按天切分到獨立物理表,查詢僅掃描目標日期分區,大幅降低 I/O 與鎖爭用。本文分享一個生產級動態存儲過程範例,展示如何在 T-SQL 中實現「按日期自動建表 → 條件判斷 → 寫入對應分表」的完整邏輯。
SocketMe20240115、MessSend20240115、SockData20240115,便於識別與維護。sysobjects,不存在才 CREATE TABLE,避免重複建表報錯。EXEC(@sql) 執行 DDL/DML。ALTER PROC [dbo].[EventInsert]
@chrTagData VARCHAR(50), -- 設備編號
@intEData INT,
@chrJZData VARCHAR(50),
@intDYData INT,
@intXHData INT,
@createdata DATETIME,
@chrtype VARCHAR(1) -- 事件類型
AS
BEGIN
SET NOCOUNT ON;
-- 1. 參數防呆
IF ISNULL(@chrtype, '') = '' RETURN;
-- 2. 取得事件標題(用於訊息內容)
DECLARE @chrTitle VARCHAR(1000);
SELECT @chrTitle = CategoryTitle FROM EventCategory WHERE CategoryID = @chrtype;
-- 3. 組裝今日分表名稱
DECLARE @chrDate VARCHAR(8) = REPLACE(CONVERT(VARCHAR(10), GETDATE(), 120), '-', '');
DECLARE @chrMetabName VARCHAR(50) = 'SocketMe' + @chrDate; -- 模擬告警表
DECLARE @chrSendTabName VARCHAR(50) = 'MessSend' + @chrDate; -- 訊息彈出表
DECLARE @chrSockDataTabName VARCHAR(50) = 'SockData' + @chrDate; -- 原始資料表
-- 4. 動態建表:模擬告警表
DECLARE @sql NVARCHAR(MAX);
SET @sql = N'
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = ''' + @chrMetabName + ''' AND type = ''U'')
BEGIN
CREATE TABLE ' + QUOTENAME(@chrMetabName) + N'(
SMeID INT IDENTITY(1,1) PRIMARY KEY,
tabname VARCHAR(50),
TagData VARCHAR(50),
TagDataMe VARCHAR(500),
Pcount INT NULL,
Content VARCHAR(5000),
UserID INT NULL,
JZData VARCHAR(50),
EData INT,
DYData INT,
XHData INT,
Type VARCHAR(1),
State VARCHAR(1),
IfClose VARCHAR(1),
CloseDate DATETIME,
CreateDate DATETIME
);
END';
EXEC (@sql);
-- 5. 動態建表:訊息彈出表
SET @sql = N'
IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE name = ''' + @chrSendTabName + ''' AND type = ''U'')
BEGIN
CREATE TABLE ' + QUOTENAME(@chrSendTabName) + N'(
MessID INT IDENTITY(1,1) PRIMARY KEY,
TabName VARCHAR(50),
TabPrID INT,
MessTitle VARCHAR(500),
TagData VARCHAR(50),
TagDataMe VARCHAR(1000),
Content VARCHAR(2000),
Type VARCHAR(1),
CreateDate DATETIME
);
END';
EXEC (@sql);
-- 6. 業務邏輯:去重檢查 → 寫入主表 → 取身份值 → 清理舊訊息 → 寫入訊息表
SET @sql = N'
IF NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(@chrMetabName) + N' WHERE TagData = ''' + @chrTagData + ''' AND Type = ''' + @chrtype + ''' AND IfClose = ''0'')
BEGIN
-- 寫入主表
INSERT INTO ' + QUOTENAME(@chrMetabName) + N' (tabname, TagData, TagDataMe, Content, JZData, EData, DYData, XHData, Type, IfClose, CreateDate, State)
SELECT ''' + @chrMetabName + ''', ''' + @chrTagData + ''', dbo.funTagDataMeget_all(''' + @chrTagData + '''),
''' + @chrTitle + N',位置:['' + dbo.funGetEvenAddget(''' + @chrJZData + ''') + '']'',
''' + @chrJZData + ''', ' + CAST(@intEData AS VARCHAR(20)) + N', ' + CAST(@intDYData AS VARCHAR(20)) + N', ' + CAST(@intXHData AS VARCHAR(20)) + N',
''' + @chrtype + ''', ''0'', GETDATE(), ''0'';
DECLARE @intSMeID INT = @@IDENTITY;
-- 清理舊訊息並寫入新訊息
DELETE ' + QUOTENAME(@chrSendTabName) + N' WHERE TagData = ''' + @chrTagData + ''' AND Type = ''' + @chrtype + ''';
INSERT INTO ' + QUOTENAME(@chrSendTabName) + N' (TabName, TabPrID, MessTitle, TagData, Content, Type, CreateDate)
SELECT ''' + @chrMetabName + ''', @intSMeID,
dbo.funTagDataMeget_all(''' + @chrTagData + ''') + ''' + @chrTitle + ''',
''' + @chrTagData + ''',
dbo.funTagDataMeget_all(''' + @chrTagData + ''') + ''' + @chrTitle + N',位置:['' + dbo.funGetEvenAddget(''' + @chrJZData + ''') + '']'',
''' + @chrtype + ''', GETDATE();
END';
EXEC (@sql);
END
QUOTENAME() 防注入動態拼接表名時使用 QUOTENAME(@var) 自動加上 [],防止異常字元導致語法錯誤或注入風險。
@@IDENTITY 取回自增鍵主表寫入後立即取得 @@IDENTITY 作為訊息表外鍵,保證關聯正確。
範例中呼叫 dbo.funTagDataMeget_all()、dbo.funGetEvenAddget() 封裝複雜格式化邏輯,存儲過程保持精簡。
生產環境建議在外層包裝顯式交易,並視情況加 TABLOCKX / HOLDLOCK 提示,避免並發建表衝突。
UPDATE STATISTICS,確保優化器有準確基數估計。UNION ALL 跨分表,或引入分區視圖/資料表分區(Enterprise Edition)實現透明查詢。動態分表存儲過程模式將「分表路由、建表、寫入」封裝於資料庫層,應用層無感知,兼具效能與維護性。配合保留策略與監控體系,可支撐億級寫入場景長期穩定運行。