Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

Detecting Changes to a Table Expand / Collapse
Author
Message
Posted Monday, May 23, 2011 6:59 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 1:43 PM
Points: 225, Visits: 306
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
Post #1113720
Posted Monday, May 23, 2011 10:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, September 22, 2012 1:03 AM
Points: 2, 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.
Post #1113743
Posted Tuesday, May 24, 2011 8:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 12:57 PM
Points: 880, Visits: 2,433
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.
Post #1114062
Posted Tuesday, May 24, 2011 12:09 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:18 PM
Points: 350, Visits: 259
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
Post #1114291
Posted Tuesday, May 24, 2011 12:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 25, 2014 7:57 AM
Points: 4, Visits: 54
If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?
Post #1114308
Posted Tuesday, May 24, 2011 3:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:36 AM
Points: 2, Visits: 6
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?
Post #1114458
Posted Wednesday, May 25, 2011 3:25 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:02 AM
Points: 60, Visits: 301
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
Post #1114591
Posted Wednesday, May 25, 2011 3:28 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:02 AM
Points: 60, Visits: 301
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
Post #1114594
Posted Wednesday, May 25, 2011 11:44 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:18 PM
Points: 350, Visits: 259
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.
Post #1114958
Posted Wednesday, May 25, 2011 2:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:36 AM
Points: 2, Visits: 6
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
Post #1115047
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse