SQL: More on finding rows that have changed using HASHBYTES and FOR JSON PATH

In a previous post, I wrote about how to determine if a set of incoming values for a row are different to all the existing values in the row, using T-SQL in SQL Server.

I later remembered that I’d seen a message by Adam Machanic a while back, talking about how FOR JSON PATH might be useful for this, so I did a little more playing around with it.

If you are using SQL Server 2016 or later, I suspect this is a really good option.

Here’s an example of using it to hash all the rows from the Purchasing.PurchaseOrders table in WideWorldImporters:

If you’re not familiar with CROSS APPLY, it’s basically used to call a table valued function (TVF) for every row of a source table. In this case, the source table is Purchasing.PurchaseOrders and for every row in that table, we’re calling the code below. It’s not really a TVF but acts as one. It can return a table of data for every incoming row from Purchasing.PurchaseOrders.

In this case, it’s being used to get all the data in the row, and then push it all into JSON. The INCLUDE_NULL_VALUES option is a key trick, as it ensures that NULL values aren’t ignored. Here’s how it would look without the hashing:

Now, note that I’ve used po.* here to get all the columns in the table. Because the hash itself would also be stored in the table, and we don’t have any syntax to say “all columns except one”, we’d likely have to list all the columns here, to avoid including the hashed value in the hash calculations.

After using the hashing though, you can see the output in the main image above.

If you are working with SQL Server 2016 or later, this could be a good option. Thanks to Adam for suggesting it.

2018-07-16