SQL Server 分區檢視表進階技巧：結構變更、插入限制與常數陷阱

前言

分區檢視表 讓大量資料橫向分散於多個成員資料表，再透過檢視表聯合查詢，實現資料分區與查詢裁剪。本文分享三個實戰技巧：避免 Enterprise Manager 修改結構導致效能退化、繞過檢視表插入限制、小心分區欄位使用常數導致裁剪失效。

技巧一：拒絕用 Enterprise Manager 異動分區結構

Enterprise Manager 修改分區資料表結構 (如加欄位) 時，會重建整個資料表並卸除/重建檢視表，導致統計資料遺失、查詢計畫失效、分區裁剪失效。請**一律使用 T-SQL 腳本** 異動結構：

-- 正確做法：逐一修改成員表，最後重新建立檢視表 (或 ALTER VIEW)
ALTER TABLE Orders2000 ADD NewCol INT NULL;
ALTER TABLE Orders2001 ADD NewCol INT NULL;
ALTER TABLE Orders2002 ADD NewCol INT NULL;
-- 檢視表若未 SELECT *，需同步 ALTER VIEW 加入新欄位

技巧二：繞過分區檢視表插入限制

分區檢視表要可更新，需滿足嚴格條件 (分區欄位為主鍵一部分、檢視表定義包含所有成員表、無彙總/Group By 等)。若無法滿足，可改用 **INSTEAD OF INSERT 觸發器** 在檢視表上攔截插入，手動導向正確成員表：

CREATE TRIGGER trg_Orders_Ins ON vw_Orders INSTEAD OF INSERT AS
BEGIN
    INSERT INTO Orders2000 SELECT * FROM inserted WHERE OrderDate < '2001-01-01';
    INSERT INTO Orders2001 SELECT * FROM inserted WHERE OrderDate >= '2001-01-01' AND OrderDate < '2002-01-01';
    INSERT INTO Orders2002 SELECT * FROM inserted WHERE OrderDate >= '2002-01-01';
END

技巧三：分區欄位勿用常數/變數導致裁剪失效

查詢條件若對分區欄位使用常數運算或隱含轉換，最佳化器無法在編譯期判斷分區範圍，導致**全表掃描**。

-- 錯誤：隱含轉換導致裁剪失效
SELECT * FROM vw_Orders WHERE OrderDate = '20010101';  -- 字串隱含轉換

-- 正確：明確型別，啟用靜態裁剪
SELECT * FROM vw_Orders WHERE OrderDate = CONVERT(DATETIME, '20010101', 112);

-- 參數化查詢 (SP/Prepared Statement) 亦可啟用動態裁剪 (SQL 2005+)
CREATE PROC GetOrders @StartDate DATETIME, @EndDate DATETIME AS
SELECT * FROM vw_Orders WHERE OrderDate >= @StartDate AND OrderDate < @EndDate;

驗證分區裁剪是否生效

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- 或在 SSMS 開啟「顯示實際執行計畫」
SELECT * FROM vw_Orders WHERE OrderDate >= '20010101' AND OrderDate < '20010201';
-- 觀察 STATISTICS IO 輸出：非目標分區的 logical reads 應為 0
-- 執行計畫中非目標分區的 Actual Executions 應為 0

結語

善用 T-SQL 控制結構變更、INSTEAD OF 觸發器突破插入限制、嚴謹撰寫分區欄位查詢條件，才能充分發揮分區檢視表的效能與擴充優勢。