SQL Server 2005 查詢通知與 SqlCacheDependency 實現資料庫緩存依賴完整教程

核心機制：Service Broker + 查詢通知

SQL Server 2005 引入 **Query Notification**：訂閱查詢結果變更通知，配合 `System.Web.Caching.SqlCacheDependency` 實現 **資料庫驅動的緩存失效**，替代傳統輪詢/時間過期策略。

四步啟用流程

Step 1：啟用 Service Broker

-- 檢查
SELECT DATABASEPROPERTYEX('MyDB', 'IsBrokerEnabled'); -- 1=啟用, 0=未啟用

-- 啟用 (需獨佔存取，建議維護窗口執行)
ALTER DATABASE MyDB SET ENABLE_BROKER;
-- 若卡住：ALTER DATABASE MyDB SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Step 2：應用啟動/關閉時註冊/解除監聽器

// Global.asax
void Application_Start() {
    SqlDependency.Start(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
}
void Application_End() {
    SqlDependency.Stop(ConfigurationManager.ConnectionStrings["MyDB"].ConnectionString);
}

Step 3：建立命令與依賴 (兩種模式)

模式 A：SqlCacheDependency + Cache.Insert (ASP.NET 快取物件)

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("SELECT Col1, Col2 FROM dbo.Table WHERE Status = 1", conn)) {
    conn.Open();
    var dep = new SqlCacheDependency(cmd);
    var onRemove = new CacheItemRemovedCallback(RemovedCallback);
    Cache.Insert("MyCacheKey", GetData(), dep, 
                 Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration,
                 CacheItemPriority.Default, onRemove);
}

模式 B：SqlDependency + OnChange 事件 (自訂邏輯/非 Web 環境)

using (var conn = new SqlConnection(connStr))
using (var cmd = new SqlCommand("SELECT Col1 FROM dbo.Table WHERE Id > @Id", conn)) {
    cmd.Parameters.AddWithValue("@Id", 100);
    conn.Open();
    var dep = new SqlDependency(cmd);
    dep.OnChange += (sender, e) => {
        if (e.Type == SqlNotificationType.Change) {
            // 清除快取、重新載入、記錄日誌...
            InvalidateCache();
        }
    };
    // 必須執行命令啟動訂閱
    using (var reader = cmd.ExecuteReader()) { /* 讀取或直接關閉 */ }
}

Step 4：查詢限制 (必須滿足才能訂閱成功)

• **不支援**：`*`、不具名欄位、匿名欄位、`UNION`、`DISTINCT`、`TOP`、`ORDER BY` (無 `TOP`)、`GROUP BY`、`HAVING`、`COMPUTE`、`CUBE`、`ROLLUP`、外部連接 (`LEFT/RIGHT/FULL JOIN`)、子查詢、`PIVOT`/`UNPIVOT`、聚合函數 (除 `COUNT_BIG(*)`)、全文檢索謂詞、`CONTAINS`/`FREETEXT`、比較 `text/ntext/image`、`xml` 型別欄位、臨時表、表變數、系統表、檢視表 (除非 `WITH SCHEMABINDING`)、`CONTEXT_INFO`、`MIN_ACTIVE_ROWVERSION`。
• **支援**：具名欄位列表、兩部分命名 (`dbo.Table`)、內部連接 (`INNER JOIN`)、`WHERE` 條件 (SARGable)、參數化查詢、`COUNT_BIG(*)` 作為選擇清單一部分。

常見坑與排查

效能與架構建議

• **訂閱粒度**：針對高頻變更表，設計細粒度查詢 (如按 `TenantId`、`Status` 分區訂閱)，避免單一大查詢頻繁失效全站快取。
• **快取層級**：L1 (記憶體 `MemoryCache`/`IMemoryCache`) + L2 (分散式 `Redis`/`NCache`)，SqlDependency 僅驅動 L1 失效，L2 由訊息佇列同步。
• **替代方案**：SQL Server 2016+ **Change Tracking / CDC** + 輪詢 / SignalR / gRPC 推播，適用於高頻寫入、需完整變更資料流場景。

結語

`SqlCacheDependency` 實現了**「資料變、快存失效」**的理想模型。關鍵在於：**Service Broker 正確啟用、查詢嚴格符合限制、應用生命週期正確管理監聽器、監控對話佇列健康度**。掌握這四點，即可構建高可靠、低延遲的資料庫驅動快取架構。

1. 上一篇文章： SQL Server 資料與應用邏輯分層架構：T-SQL 集合操作 vs .NET 列操作的抉择指南
2. 下一篇文章： SQL Server DBA 必備 T-SQL 工具箱：13 類常用管理腳本速查手冊

關於SqlServer

SQL Server 2000 升級 2005 後無法刪除使用者完整解決方案

問題背景 將 SQL Server 2000 資料庫強制升級

SQL Server 內存被系統進程擠壓案例分析：Remote Registry 服務內存洩漏導致 Target Memory 驟降

案例背景 服務器：64GB 物理內存，SQL Serv

MS SQL Server 觸發器管理全攻略：查看、修改、刪除與系統預存程式

掌握觸發器的管理工具與系統預存程式，是資料庫開發與維運的必備

VB6 結合 ADO 實作 SQL Server 2000 資料庫備份與還原完整模組代碼

模組一：資料庫備份函數 fBackupDatabase_a