How do you know if a SQL table has been changed since the last time you used it?
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?
Answer
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.