How to detect modifications in SQL Server database table?

Data and Databases
1

I need to track changes in a SQL Server database table without using triggers or making any database structure modifications. I’m working with .NET and C# as my development stack.

My application needs to work with SQL Server 2000 SP4 and later versions. This is a third-party integration project where I’m building a data visualization add-on. Since we have thousands of customers, I can’t require database schema changes at each deployment site.

When I say “changes” I’m referring to data modifications, not structural alterations to the table itself. I want these changes to fire events in my app rather than polling at regular intervals.

I’m thinking about using the CHECKSUM_AGG approach with BINARY_CHECKSUM function. My plan is to run this query periodically:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*))
FROM target_table
WITH (NOLOCK);

Then compare the result with my cached value. When changes are detected, I’d scan individual rows using:

SELECT record_id, BINARY_CHECKSUM(*)
FROM target_table
WITH (NOLOCK);

This would let me identify exactly which rows changed by comparing checksums.

2

good point! just keep in mind that row count checks are good, but they might not catch every case. using a ROWVERSION column is definitely a safer bet if you can. it adds a bit more reliability to your tracking.

3

Nice checksum approach! But what if two differnt row changes produce the same checksum? How do you handle collisions when they pop up?

4

Your checksum strategy works well - I’ve encountered similar issues with legacy system integrations. BINARY_CHECKSUM is solid for catching changes, but I’d recommend adding a quick row count check first. This will immediately identify insertions and deletions without taxing the table excessively with checksum calculations.

Given that you’re working with SQL Server 2000, consider the @@DBTS system function. It provides the current timestamp and indicates when any modifications occur in the database. While it’s not as detailed as modern change tracking, it gets the job done. Just be mindful when setting your baseline, as it captures all database activity.

From a performance standpoint, using CHECKSUM_AGG with NOLOCK can still be demanding on large tables. I suggest employing a tiered approach—start with lightweight checks, and resort to full checksums only when necessary. This keeps overhead manageable during quiet periods while still meeting your event-driven needs.