title: 巧用columns_updated擷取資料變更
在平時與資料庫打交道的過程中，我們經常會有這樣的疑惑：如何快速的擷取資料變更記錄呢？舉個例子，搜尋引擎要為外部客人提供快速準确的商品資訊搜尋功能，那麼當有新的商品資料變更後，搜尋引擎如何快速的發現這些新的變更資料呢？我們常見的兩種做法：
這種方法最為簡單直接，反正不管三七二十一，搜尋引擎每次全量拉取商品資訊表所有資料，然後建立搜尋索引，提供給外部客人查詢。這種方法實作起來的确最為簡單，當然同時也具有非常明顯的缺點：
浪費資源： 假如商品的變更頻率為20%，那麼剩下的80%商品實際上是不需要更新的。換句話說全量更新會浪費掉80%的系統資源(io/cpu/memory)來做無用功。
耗時嚴重： 由于擷取的是表的全量資料，是以全量更新大大增加了資料擷取階段和搜尋索引生成階段鎖的機率，加之浪費資源做無用功，最終導緻時間消耗大大拉長。
資料更新時效性差： 由于耗時嚴重，是以導緻資料更新不及時，時效性差，随着商品量的不斷擴大，這種時效性會越來越差，最終導緻客戶抱怨。
針對全量更新的種種“罪行”，我們可以有針對性的采用全量+增量更新的方式來有效解決。這種方法的思路是，我們可以周期性的做全量更新，比如每天或者每周，然後在兩個全量更新周期之間，我們采用增量更新的方式來覆寫新的資料變更，比如每小時或者每分鐘。增量更新問題的關鍵在于如何擷取資料變更記錄，讓我們來看看關系型資料庫mssql server是如何提供解決方法的。
mssql server提供了一個函數，名為columns_updated可以解決這個問題。先讓我們來看看微軟官方的解釋：傳回 varbinary 位模式，它訓示表或視圖中插入或更新了哪些列。官方文檔的解釋非常的抽象，如果想要使用這個函數來擷取資料變更記錄，我們需要踩過很多坑，突破很多點，這也是這篇文章的價值。
首先，我們來看看這個函數表達的含義。假如某張表有8個字段，那麼columns_updated使用一個byte，八個bit來表示哪些列發生了資料變更，表示方法如下：
col_id
8
7
6
5
4
3
2
1
bit
value
128
64
32
16
col_id：表字段順序id
bit：bit位順序，從0開始
value：2的bit次方
當某些列被更新後，columns_updated函數會傳回varbinary位模式（varbinary位模式是什麼？可以了解為所有列value的sum值的二進制格式）。比如：當第二列和第四列被更新，那麼columns_updated的varbinary位模式是2 + 8 = 10。來看一個具體的例子。
結果如下：
注意上面的代碼power(2, column_id - 1)，傳回的應該是一個int資料類型的值。在mssql sql server中int類型使用4個位元組來存儲，也就是32bit，換句話說，當表的字段列個數達到32時，這個power操作會導緻int資料類型溢出而報告異常。當我們将上面的表字段加到32個後，insert和update操作會導緻trigger報告如下錯誤：
關于這個問題，在沒有完美的解決方法之前，很長一段時間，我們強制将power轉化為bigint資料類型來暫時突破32個字段數量限制。但是，這個坑原理和上面一樣，僅僅是将字段數量從32個擴大到64個。方法如下：
如何完美的解決上面兩個坑，我們先暫時留個懸念。
讓我們回到最原始的需求，對于dml操作，不外乎三種，即insert，update和delete。我們的trigger必須具備識别這三種操作類型的能力。
insert：trigger需要具備識别表資料行唯一辨別(rid)的能力（通常是主鍵），然後通過rid反過來查詢正式表即可。
update：trigger需要具備識别哪些字段被更新的能力，然後通過rid擷取這些被更新的字段的值。
delete：trigger擷取到資料行唯一辨別即可，通過rid删除對應的行。
綜合了所有這些分析以後，我們可以使用如下的trigger來捕獲資料變更。
最後一條查詢語句結果如下截圖：
rowid 1-3:表無主鍵，但存在identity屬性列的情況，rid為identity屬性列的值，我們抓取到的rid和updated_columns
rowid 4-6:表無主鍵，但存在unique限制的情況，rid為unique列的值，取到的rid和updated_columns
rowid 7-9:表有主鍵，這裡是更加複雜的聯合主鍵，rid為聯合主鍵的值，取到的rid和updated_columns
在本例的表字段個數超過了64個，達到73個，我們是采用循環擷取的方式來踩過坑1和2，具體代碼268行到307行。
到目前為止，我們的搜尋引擎隻需要從dbo.triggereddatalog表中擷取資料變更rid和相應發生了變化的字段updated_columns，而不需要從正式表中整個拉取全量資料，節約了資料庫系統開銷，增加了搜尋索引建立的時效性，提高了客戶體驗。
注意：
這裡需要特别提醒，正式表dbo.employeedata上千萬不要使用truncate table的操作，因為truncate動作無法激活觸發器。