How do you know if a SQL table has been changed since the last time you used it?

Question

Is there a way to know if the data in a SQL Server 2008 R2 table has changed since the last time you used it? I would like to know of any type of change -- whether a new record has been inserted or an existing one has been modified or deleted.

I am not interested in what the particular change might have been. I am only interested in a boolean value that indicates whether or not the table data has been changed.

Finally, I want a simple solution that does not involve writing a trigger for each CRUD operation and then have that trigger update some other log table.

I have C# program that is meant to insert a large amount of initial data into some database tables. This is a one off operation that is supposed to happen only once, or rarely ever again if ever, in the life of the application. However, during development and testing, though, we use this program a lot.

Currently, with about the 10 tables that it inserts data into, each having about 21,000 rows per table, the program takes about 45 seconds to run. This isn't really a huge problem as this is a one-off operation that is anyway going to be done internally before shipping the product to the customer.

Still, I would like to minimize this time. So, I want to not insert data into a table if there has been no change in the table data since my program last used it.

My colleague told me that I could use the CHECKSUM_AGG function in T-SQL. My question(s) are:

1) If I compute the CHECKSUM_AGG(Cast(NumericPrimaryKeyIdColumn AS int)), then the checksum only changes if a new row has been added or an existing one deleted, right? If someone has only modified values of other columns of an existing row in the table, that will have no impact on the checksum aggregate of the ID column, right? Or will it?

2) Is there another way I can solve the problem of knowing whether table data has changed since the last time my program used it?

Solution 1

This is very close to what I already had in mind and what @user3003007 mentioned.

One way I am thinking of is to take a CHECKSUM(*) or CHECKSUM(Columns, I, Am, Interested, In) for each such table and then do an aggregate checksum on the checksum of each row, like so:

SELECT CHECKSUM_AGG(CAST(CHECKSUM(*) as int)) FROM TableName;

This is still not a reliable method as CHECKSUM does not work on some data types. So, if I have my column of type text or ntext, the CHECKSUM will fail.

Fortunately for me, I do not have such data types in the list of columns I am interested in, so this works for me.

Solution 2

Have you investigated Change Data Capture?

Solution 3

You can use a combination of hashing and checksum_agg. The below will work as long as the the string values do not overflow the HASHBYTES function. It works by converting all of the columns to strings, concatenating those, hashing the concatenated string, turning the hash into an integer, placing all of those values into a temp table, and then running checksum_agg on the temp table. Could easily be adapted to iterate across all real tables

Edit: Combining MD5 and checksum_agg looks like it works at least for somewhat narrow tables:

declare @tablename sysname
set @tablename  = 'MyTableName'

declare @sql varchar(max) 

set @sql = 'select convert(int,HASHBYTES(''MD5'','''''


declare c cursor for
select column_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @tablename


open c

declare @cname sysname

fetch next from c into @cname

while @@FETCH_STATUS = 0 
begin
    set @sql = @sql + '+ coalesce(convert(varchar,' + @cname + '),'')'
    fetch next from c into @cname
end


close c
deallocate c

set @sql =  @sql + ')) as CheckSumVal
into ##myresults from ' + @tablename

print @sql

exec(@sql)



select CHECKSUM_AGG(CheckSumVal) from ##myresults
drop table ##myresults

Comments

[Water Cooler v2]

Thank you. Interesting. Though it looks like a thing that a DBA would do to a whole database. I am working with a client's database that I do not have much leverage on except at the application level.

[Water Cooler v2]

Thank you. My knowledge of T-SQL is fairly limited and I haven't been keeping up much. So, I don't understand the second option you mention. Also, for the first option -- how do I get an integer expression for all the columns in the table?