What are the performance implications of using checksums vs change tracking for SQL Server

Question

Back in the day, an implementation with checksum_agg was made to check if "something changed" in a table in a SQL Server table, to then make updates in another database. The users want to get the changes across between the systems quickly, so the table is read every minute to calculate the checksum. Multiply this with a number of other tables like this one, and then a number of customers, and it means a lot of data has to be read in order to continually calculate these checksums.

I'm thinking it would be a lot less resource intensive to use change tracking. It would potentially still mean asking the database every minute if something changed. But it seems that checking for new rows in the change table would be a much cheaper operation than calculating checksums for all the existing rows in the data tables.

Is the difference in performance between using checksum calculations and change tracking worth pursuing? Of course "it depends", but on what?

Answer 1

Is the difference in performance between using checksum calculations and change tracking worth pursuing?

Yea, most likely in your use case. This is because of the frequency of which you're checking for changes. Here's some benefits of why:

• It would be quicker to read materialized data from a change table instead of calculating the checksum every time

• Which would only be the things that changed, so you're not reading the entire table's worth of data

• And since it's a separate table that stores the changes, you reduce blocking on the main table, especially since you have a frequency of reading for the changed data every minute

• Also, the checksum functions are not perfect, and have potential for collisions and bugs

So yes, I'd advise on not trying to reinvent the wheel, as the checksum functions weren't implemented for the purpose of change tracking. The feature Change Tracking (or Change Data Capture or Temporal Tables) was.