Detecting Changes to a Table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Lawrence Moore » Detecting Changes to a Table

31 posts, Page 2 of 4

««1 2 3 4 »»»

Detecting Changes to a Table

Rate Topic

Display Mode

Topic Options

Author

Message

HansB

Posted Monday, May 23, 2011 10:38 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 12:55 PM
Points: 225, Visits: 211

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

virtualjosh

Posted Monday, May 23, 2011 10:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:29 AM
Points: 5, Visits: 85

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

Lawrence Moore

Posted Monday, May 23, 2011 10:49 AM

Valued Member

Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58, Visits: 262

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. Wink

Many thanks,

Lawrence

Post #1113477

Lawrence Moore

Posted Monday, May 23, 2011 10:57 AM

Valued Member

Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58, Visits: 262

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

Eric M Russell

Posted Monday, May 23, 2011 11:07 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 1,162, Visits: 3,333

virtualjosh (5/23/2011)

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.

"Wise people understand the 10,000 things without going to each one.
They know them without having to look at each one,
and they transform all without acting on each one." - The Tao Te Ching: Verse 47

Post #1113492

Lawrence Moore

Posted Monday, May 23, 2011 2:12 PM

Valued Member

Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58, Visits: 262

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

S. Kusen

Posted Monday, May 23, 2011 2:41 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 09, 2013 2:25 PM
Points: 956, Visits: 873

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

Eric M Russell

Posted Monday, May 23, 2011 2:49 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 2:02 PM
Points: 1,162, Visits: 3,333

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

virtualjosh

Posted Monday, May 23, 2011 3:05 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 27, 2013 10:29 AM
Points: 5, Visits: 85

Lawrence/Eric:

You guys are right. Thanks for pointing that out to me.

Post #1113643

codebyo

Posted Monday, May 23, 2011 6:34 PM

SSC Eights!

Group: General Forum Members
Last Login: Thursday, May 16, 2013 5:30 AM
Points: 861, Visits: 1,436

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 »

31 posts, Page 2 of 4

««1 2 3 4 »»»

Permissions