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 10:38 AM


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,

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.
Post #1113471
Posted Monday, May 23, 2011 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 10:35 AM
Points: 5, Visits: 91
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.
Post #1113475
Posted Monday, May 23, 2011 10:49 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 60, Visits: 308
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
Post #1113477
Posted Monday, May 23, 2011 10:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 60, Visits: 308
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

Post #1113486
Posted Monday, May 23, 2011 11:07 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1113492
Posted Monday, May 23, 2011 2:12 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 60, Visits: 308
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.
Post #1113603
Posted Monday, May 23, 2011 2:41 PM
SSC Eights!

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

Group: General Forum Members
Last Login: 2 days ago @ 12:25 PM
Points: 969, Visits: 974
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
Post #1113625
Posted Monday, May 23, 2011 2:49 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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.
Post #1113632
Posted Monday, May 23, 2011 3:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 10:35 AM
Points: 5, Visits: 91
Lawrence/Eric:

You guys are right. Thanks for pointing that out to me.
Post #1113643
Posted Monday, May 23, 2011 6:34 PM


SSC Eights!

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

Group: General Forum Members
Last Login: Monday, November 10, 2014 3:37 AM
Points: 904, Visits: 1,491
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
Post #1113713
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse