Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Detecting Changes to a Table


Detecting Changes to a Table

Author
Message
HansB
HansB
SSC Veteran
SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)SSC Veteran (231 reputation)

Group: General Forum Members
Points: 231 Visits: 442
Lawrence.
It always goes back to the same question, what do we need to accomplish? In some cases CHECKSUM might be a valid option, (I am having a hard time coming up with an example in its favor, but that has more to do with the index scan than the symmetric update issue.) but there might be scenario's where this is a valid solution, as triggers might do the trick for (probably most) other people.
I was merely pointing out that the standard audit columns would alleviate the symmetric update problem, I for one still would use triggers.
Again, I do appreciate you taking the time you took, and sharing your stuff with us!
Hans
simson2010
simson2010
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 8
I think if we add one more column to CheckSumTest Table - LstChgDate DateTime, and we update it every operation, insert, update etc.

The CheckSum_Agg() will also return diff value when symmetric update.:-P
Nadrek
Nadrek
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1035 Visits: 2673
1) Index usage DMV's are emptied when SQL Server is restarted http://msdn.microsoft.com/en-us/library/ms188755.aspx

2) When properly, precisely done checksums (all columns fixed width or very, very careful delimiting (CHAR(31), perhaps), COALESCE/ISNULLed to an impossible value, etc.) can tell you:
2a) If two rows are different
2b) If you need to check further into whether two rows are the same or not; regardless of the theoretical collision chances based on statistical assumptions, I've seen many reports of actual collisions, particularly since real (unencrypted) data very rarely looks random; you can get "Bobs" "cars" and "Bob" "scars" easily enough (see prior comments about properly and precisely done).

3) Lastupdate type columns yields false "the data changed" results if a table was rebuild even when all data except the lastupdate column is 100% identical.

4) For full comparisons, I prefer something like:

WHERE (
((Old.[field1] <> New.[field1]) OR ((Old.[field1] IS NULL) AND (New.[field1] IS NOT NULL)) OR ((Old.[field1] IS NOT NULL) AND (New.[field1] IS NULL)))
OR ((Old.[field2] <> New.[field2]) OR ((Old.[field2] IS NULL) AND (New.[field2] IS NOT NULL)) OR ((Old.[field2] IS NOT NULL) AND (New.[field2] IS NULL)))
)



Note that for tables with many columns, having an automated way to build these statements is much better than copy/paste/modify.

For anyone that insists on using checksums, I would recommend a scheduled process (weekly, monthly, quarterly) that does a thorough check for collisions. Gamblers hope for good look... we should check regularly for bad luck.
JChrisCompton
JChrisCompton
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 283
If you understand what a checksum is then you know whether it is appropriate to use it. It is a type of hash, so sometimes it is appropriate and sometimes it isn't.
IMHO the business rules will dictate the usefulness rather than the collision probability.

I think it would be appropriate for BOL to point that out and either provide a reference to additional information or (2) explain how the aggregate is computed.

Good article though, created a great discussion, I'm giving you a 5 Cool
fmendes
fmendes
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 54
If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?
arsinfor
arsinfor
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
I'm looking for the most eficcient way to audit the changes on a particular table. Generaly you need to know which table, row and column where changed. I mean the option is triggers but expend very much time to create one by one on each table. Also we need to check inserted against deleted temp row column by column...
Does exist another way?
Lawrence Moore
Lawrence Moore
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 400
fmendes (5/24/2011)
If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?


Yes I believe you are right, but this raises the question of what value the CHECKSUM_AGG() is bringing to the party, when you can simply use [SELECT MAX(rowversion) from TABLE] as your "has the table changed algorithm", which could be more efficient with an appropriate index.

Best regards,

Lawrence
Lawrence Moore
Lawrence Moore
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 400
arsinfor (5/24/2011)
I'm looking for the most eficcient way to audit the changes on a particular table. Generaly you need to know which table, row and column where changed. I mean the option is triggers but expend very much time to create one by one on each table. Also we need to check inserted against deleted temp row column by column...
Does exist another way?


Hi arsinfor,

If you are using SQL 2008, then I believe the new change tracking features could be useful given your requirements.

http://msdn.microsoft.com/en-us/library/cc280462.aspx

However, once you start to talk about custom logic based around inserted/deleted rows, then I think you are in trigger territory.

Hope this helps,
Lawrence
JChrisCompton
JChrisCompton
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 283
fmendes (5/24/2011)
If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?


Nope, not "always".
Yes, it will (almost always) fix the example given for 'symmetric change' (I think he called it)

But, it is a hash - so there are collisions.
A hash on a table that could contain millions of rows and a couple hundred columns is condensed (hashed) down to a single scalar value which is the hash value. So, logically there are some hash values that have more than one source.

(There are hashes which are 'perfect hashes' but those are restricted to a specific set of data and the source data must be known in advance. Not possible in this discussion)

HTH,
-Chris C.
arsinfor
arsinfor
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 8
Thanks very much Lawrence.

I am using sql 2005 and I find in this address a possible solution.....
http://weblogs.asp.net/jgalloway/archive/2008/01/27/adding-simple-trigger-based-auditing-to-your-sql-server-database.aspx
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search