SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Lawrence Moore / Detecting Changes to a Table / Latest Posts

RE: Detecting Changes to a Table

Lawrence Moore — Thu, 26 May 2011 03:57:22 GMT

[quote][b]arsinfor (5/25/2011)[/b][hr]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[/quote]This looks like a nice automated solution, based on triggers. Good luck!

RE: Detecting Changes to a Table

arsinfor — Wed, 25 May 2011 14:22:53 GMT

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

RE: Detecting Changes to a Table

Chris.C-977504 — Wed, 25 May 2011 11:44:40 GMT

[quote][b]fmendes (5/24/2011)[/b][hr]If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?[/quote]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.

RE: Detecting Changes to a Table

Lawrence Moore — Wed, 25 May 2011 03:28:28 GMT

[quote][b]arsinfor (5/24/2011)[/b][hr]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?[/quote]Hi arsinfor,If you are using SQL 2008, then I believe the new change tracking features could be useful given your requirements.[url=http://msdn.microsoft.com/en-us/library/cc280462.aspx]http://msdn.microsoft.com/en-us/library/cc280462.aspx[/url]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

RE: Detecting Changes to a Table

Lawrence Moore — Wed, 25 May 2011 03:25:50 GMT

[quote][b]fmendes (5/24/2011)[/b][hr]If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?[/quote]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

RE: Detecting Changes to a Table

arsinfor — Tue, 24 May 2011 15:55:50 GMT

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?

RE: Detecting Changes to a Table

fmendes — Tue, 24 May 2011 12:22:33 GMT

If we add a timestamp/rowversion column will the CHECKSUM_AGG() not always give accurate results?

RE: Detecting Changes to a Table

Chris.C-977504 — Tue, 24 May 2011 12:09:00 GMT

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:

RE: Detecting Changes to a Table

Nadrek — Tue, 24 May 2011 08:18:01 GMT

1) Index usage DMV's are emptied when SQL Server is restarted [url=http://msdn.microsoft.com/en-us/library/ms188755.aspx]http://msdn.microsoft.com/en-us/library/ms188755.aspx[/url]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:[code]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))))[/code]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.

RE: Detecting Changes to a Table

simson2010 — Mon, 23 May 2011 22:02:21 GMT

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

RE: Detecting Changes to a Table

HansB — Mon, 23 May 2011 18:59:44 GMT

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

RE: Detecting Changes to a Table

codebyo — Mon, 23 May 2011 18:34:44 GMT

Although you may use better methods in SQL Server 2008 now, there are many companies which still run MSSQL 2000/2005 and I appreciate the time you took to write this for us.Thank you.

RE: Detecting Changes to a Table

virtualjosh — Mon, 23 May 2011 15:05:24 GMT

Lawrence/Eric:You guys are right. Thanks for pointing that out to me.

RE: Detecting Changes to a Table

Eric M Russell — Mon, 23 May 2011 14:49:48 GMT

[quote][b]Lawrence Moore (5/23/2011)[/b][hr]Hi Eric,Many thanks for your post.It is true that DMVs offer lots of useful information, some of which could be applied for requirements discussed in my article.However, DMVs typically require elevated user permissions, such as VIEW SERVER STATE.Regards,Lawrence[/quote]I'd think that a process, which does something like querying for changes in a table and selecting the rows out to another table, would not be running under an application or user account. It would probably the SSIS or the SQL Agent account, in which case it would be OK to grant it VIEW SERVER STATE permission.

RE: Detecting Changes to a Table

S. Kusen — Mon, 23 May 2011 14:41:48 GMT

In 2008 and 2008 R2 (might only be enterprise), there is the change data capture configuration that could also be put in place.http://msdn.microsoft.com/en-us/library/bb522489.aspxRegards,Steve

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 14:12:35 GMT

HansB,BTW I was thinking more about your post, and it also occurs to me that if you have a datetime column maintained by triggers, then you could simply use the query: [SELECT MAX(<datetimecol>) FROM Table] as your algorithm for detecting changes to the table, rather than bothering to use a CHECKSUM based approach.Best regards,Lawrence.

RE: Detecting Changes to a Table

Eric M Russell — Mon, 23 May 2011 11:07:21 GMT

[quote][b]virtualjosh (5/23/2011)[/b][hr]I use system tables to see if the table has been updated:SELECT @expiration_dt = [modify_date]FROM [mydb].[sys].[tables]WHERE [name] = 'mytable'If I detect @expiration_dt to be newer than my stored data (which obviously is datetime'd), then I rerun my code.[/quote]The modified_date column on the sys.tables or sys.objects catalog views contains the date/time the schema for an object was last altered. For example, if you add a new column. It doesn't contain the date/time of the last insert/update/delete.

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 10:57:00 GMT

Hi virtualjosh,I'd be very careful using the sys.tables.modify_date field.In my experience, it is not always kept up to date in realtime.For example, try the following:CREATE TABLE test1 (i INT, vc1 VARCHAR(10))SELECT modify_date FROM sys.tables WHERE name='test1'INSERT test1 VALUES (1, 'row1')SELECT modify_date FROM sys.tables WHERE name='test1'The values returned are the same....(?)Regards,Lawrence

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 10:49:14 GMT

Thanks HansB,It's a very good point you raise. Of course you are correct. However, I think it's still worthwhile highlighting the shortcomings of the CHECKSUM functions to further encourage the "best practice" approach to be followed. ;-)Many thanks,Lawrence

RE: Detecting Changes to a Table

virtualjosh — Mon, 23 May 2011 10:48:12 GMT

I use system tables to see if the table has been updated:SELECT @expiration_dt = [modify_date]FROM [mydb].[sys].[tables]WHERE [name] = 'mytable'If I detect @expiration_dt to be newer than my stored data (which obviously is datetime'd), then I rerun my code.

RE: Detecting Changes to a Table

HansB — Mon, 23 May 2011 10:38:07 GMT

Lawrence, Thanks for taking the time to write this.I however do not totally agree. While in theory you are correct, best practise is off course to have a update datetime field and probably also a updated by column on tables. your stored procs or triggers should always update these fields.This should always give you a different checksum.Again theoretically you are right, but common "best practise" reality checksum is a viable option to track table changes.H.

RE: Detecting Changes to a Table

fmendes — Mon, 23 May 2011 08:13:36 GMT

Thanks! You're correct.I should have thought of timestamp/rowversion instead of datetime.

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 08:06:03 GMT

Hi Eric,Many thanks for your post.It is true that DMVs offer lots of useful information, some of which could be applied for requirements discussed in my article.However, DMVs typically require elevated user permissions, such as VIEW SERVER STATE.Regards,Lawrence

RE: Detecting Changes to a Table

Eric M Russell — Mon, 23 May 2011 07:45:32 GMT

SQL Server maintains statistics, which includes counts and timestamps, whenever table indexes are updated. This meta data can be queried from an interesting data management view called sys.dm_db_index_usage_stats. For some situations this would suit the purpose of detecting table changes. For example:[code="sql"]select object_name(s.object_id) as table_name, i.name as index_name,last_user_update, user_updates from sys.dm_db_index_usage_stats as s join sys.indexes i on i.object_id = s.object_id and i.index_id = s.index_id where object_name(s.object_id) = 'InvHeader';[/code][code="plain"]table_name index_name last_user_update user_updates---------- ----------------- ----------------------- ------------InvHeader pk_invheader 2011-05-20 15:50:07.210 3713InvHeader uix_invheader 2011-05-19 19:15:01.370 371[/code]There are other columns in this view that return the number of seeks, scans, etc. so it can also be levereaged to determine how often indexes or tables are being accessed.

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 06:30:57 GMT

Hi fmendes,That would cover inserted rows only, but not cater for updates on the row, nor row deletions.Regards,Lawrence.

RE: Detecting Changes to a Table

fmendes — Mon, 23 May 2011 06:29:25 GMT

What if you add a column UPDATED_ON of type datetime with default to GETDATE() ?I suppose that it would make it work.

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 05:09:54 GMT

Agreed. I believe that the change tracking functionality is designed primarily to act at a lower level of granularity, so that individual row changes to a table can be audited, but I imagine you could also use it to provide an aggregated, summary "table level" view to judge if any changes have been performed across the whole table.Thanks for pointing this out.Regards,Lawrence

RE: Detecting Changes to a Table

jongy — Mon, 23 May 2011 04:59:54 GMT

Lawrence,But do you then agree that the MSDN article outlines a third method for change tracking additional to the ones discussed in the SQL Central article or am I missunderstanding anything here?/jongy

RE: Detecting Changes to a Table

Lawrence Moore — Mon, 23 May 2011 04:55:15 GMT

Hi jongy,I'm afraid I am not very familiar with change tracking.I also skim read the article you listed, but can see no mention of the CHECKSUM functions discussed in this article.Regards,Lawrence

RE: Detecting Changes to a Table

jongy — Mon, 23 May 2011 03:47:49 GMT

In this MSDN article there is information about what I understand is an "native" way of doing change tracking in relation to building applications for Sync Framework in SQL Server 2008, it that the CHECKSUM(), BINARY_CHECKSUM(), and CHECKSUM_AGG() functions mentioned in the article or is it a third way?[url=http://msdn.microsoft.com/en-us/library/cc305322.aspx]How to: Use SQL Server Change Tracking http://msdn.microsoft.com/en-us/library/cc305322.aspx[/url]

Detecting Changes to a Table

Lawrence Moore — Sun, 22 May 2011 09:52:39 GMT

Comments posted to this topic are about the item [B]Detecting Changes to a Table[/B]