SQL Clone
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 (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 452
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.
virtualjosh
virtualjosh
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 98
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.
Lawrence Moore
Lawrence Moore
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 400
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
Lawrence Moore
Lawrence Moore
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 400
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
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12147 Visits: 10637
virtualjosh (5/23/2011)
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.

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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Lawrence Moore
Lawrence Moore
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 400
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>Wink FROM Table] as your algorithm for detecting changes to the table, rather than bothering to use a CHECKSUM based approach.

Best regards,

Lawrence.
S. Kusen
S. Kusen
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1578 Visits: 1117
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.aspx

Regards,
Steve
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12147 Visits: 10637
Lawrence Moore (5/23/2011)
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

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.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
virtualjosh
virtualjosh
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 98
Lawrence/Eric:

You guys are right. Thanks for pointing that out to me.
Andre Guerreiro
Andre Guerreiro
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1335 Visits: 1515
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.

Best regards,

Andre Guerreiro Neto

Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
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