Discrepancy in CHECKSUM_AGG Results Between SQL Server 2016 and 2022

Question

Dear Microsoft Support,

I am writing to inquire about a discrepancy I have encountered while migrating a database from SQL Server 2016 to SQL Server 2022. I am utilizing the CHECKSUM_AGG function to verify data integrity during the migration process, however, I am obtaining different results for the checksum values between the two versions.

I would like to understand if there have been any alterations in the calculation algorithm for the CHECKSUM_AGG function or changes in the underlying data structures that could account for this discrepancy.

I have provided the following details to assist in troubleshooting:

• Specific query:

            Select CHECKSUM_AGG(checksum(*))

        From SYS.Objects;    

I have already reviewed the Microsoft documentation for CHECKSUM_AGG but have not found a clear explanation for this behavior.

Any insights or guidance you can provide would be greatly appreciated.

Thank you for your prompt attention to this matter.

Sincerely,

Shay

Answer 1

I have already reviewed the Microsoft documentation for CHECKSUM_AGG but have not found a clear explanation for this behavior.

You are on a wrong track, the behavior hasn't change.

You query all objects from sys.objects and if MS had added some internal objects like SP, views etc., then of course you get a different result for your query.

Answer 2

Hi @Shay

Have you check the changes of the compatibility level or default settings between these two versions? It might affect the execution plan or internal optimizations.

In addition, SQL Server 2022 includes various improvements in query optimization, parallelism, and execution plans, which may affect how CHECKSUM_AGG aggregates data.

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".