SQL: Even more details on finding rows that have changed using HASHBYTES and FOR JSON PATH
In a previous post, I wrote about how to create a hash of all the columns in a table, by using FOR JSON PATH and HASHBYTES. This is incredibly useful if you need to check if incoming data is different to existing table data.
The code that I suggested (based on WideWorldImporters) was as follows:
SELECT po.PurchaseOrderID,
HASHBYTES('SHA2_256', pod.PurchaseOrderData) AS HashedData
FROM Purchasing.PurchaseOrders AS po
CROSS APPLY
(
SELECT po.*
FOR JSON PATH, ROOT('Purchase Order'), INCLUDE_NULL_VALUES
) AS pod(PurchaseOrderData);
The challenge with that code though, is that for the existing table data, it’s best calculated when the data is stored, rather than every time it’s queried. And, bonus points if you then create an index that holds just the key for matching plus an included column for the HashedData. With a bit of careful work, you can get an efficient join happening to find differences.
2023-10-09