# 技術分享 | MS-SQL使用 json 取代 正規化欄位紀錄資料交易,及如何比對差異資料

### 會議日期: 2022-04-22
* [SQL Server JSON Diff. Checking for differences between JSON documents.](https://www.red-gate.com/simple-talk/blogs/sql-server-json-diff-checking-for-differences-between-json-documents/)
* 處理流程概述
* 要紀錄的物件先序列化成 JSON (建立到2層就好, 多層可能不適合.)
* 資料庫開 nvarchar(max) ~~或~~ ~~ntext~~ 把 JSON 塞進去
(感恩 CHAN CHAN 提供資訊)
* 資料庫建立資料表值函式,參考 [Compare_JsonObject 函數.sql](https://www.red-gate.com/simple-talk/blogs/sql-server-json-diff-checking-for-differences-between-json-documents/)
* 利用For JSON語法,比對 json 差異(SQL 2016 後支援)

    declare @SourceJSON nvarchar(max)
    declare @TargetJSON nvarchar(max)
    SET @SourceJSON = (SELECT LogBody FROM CommonLog  WHERE LogID='83F2A2AB-FE1B-4F2B-ADDF-8E7070BF2B08') --來源(原來)
    SET @TargetJSON = (SELECT LogBody FROM CommonLog  WHERE LogID='D1DB7497-5B12-4D38-918D-26F487710D20') --比對(現在)
    SELECT SideIndicator, ThePath, TheKey, TheSourceValue, TheTargetValue
    FROM dbo.Compare_JsonObject(@SourceJSON, @TargetJSON) AS Diff;

* 注意事項
* 如果紀錄量體大，考慮將紀錄用 DB 與商業邏輯 DB 分開存放，未來在處理備援或備份的時候，可以針對主體資料，跟流水帳的紀錄資料做不同的處理策略與頻率。
* 如果要節省資料庫空間，除了 nvarchar , ntext 型態，也可以考慮把物件序列化成 BSON 型態，資料庫開 Binary 型態去貯存，不過這樣的方式後續要讀取資料時，透過 TSQL 可能就不太方便了，所有的事情都需要透過程式讀取跟處理。
* 補充資料
* SQL 2016 後雖然有支援 JSON ，但本質還是文字欄位。有時看看窗戶外面的世界 Postgresql 早在 9.2 版就支援 JSON 跟 JSOB 的欄位型態，有興趣的人去查一下，那邊在 DB 層級對 JSON 的操作更完善，甚至支援某些程度的 Index 建置。
* 未啥不要在用ntext。因為微軟未來不支援囉。
[ntext、text 和 image (Transact-SQL)](https://docs.microsoft.com/zh-tw/sql/t-sql/data-types/ntext-text-and-image-transact-sql?view=sql-server-ver15)
* [jsondiffpatch](https://github.com/benjamine/jsondiffpatch)