Mastering SQL Server 2017

Returning the difference between two table rows

Another example where you can use OPENJSON is to return the difference between two rows in a table. For instance, when you put application settings for different environments in a database table, you might need to know what is different in the settings between the two environments. You can accomplish this task by comparing values in each column, but this can be annoying and error prone if the table has many columns.

The following example returns the difference for database settings in the master and model database in an instance of SQL Server 2017:

SELECT mst.[key], mst.[value] AS mst_val, mdl.[value] AS mdl_val FROM OPENJSON ((SELECT * FROM sys.databases WHERE database_id = 1 FOR JSON AUTO, ...