|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58,
Visits: 262
|
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Tuesday, May 24, 2011 11:22 PM
Points: 2,
Visits: 11
|
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58,
Visits: 262
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Tuesday, May 24, 2011 11:22 PM
Points: 2,
Visits: 11
|
|
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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58,
Visits: 262
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, November 28, 2012 9:36 PM
Points: 4,
Visits: 51
|
|
What if you add a column UPDATED_ON of type datetime with default to GETDATE() ? I suppose that it would make it work.
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58,
Visits: 262
|
|
Hi fmendes,
That would cover inserted rows only, but not cater for updates on the row, nor row deletions.
Regards,
Lawrence.
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Today @ 6:59 AM
Points: 1,164,
Visits: 3,338
|
|
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:
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';
table_name index_name last_user_update user_updates ---------- ----------------- ----------------------- ------------ InvHeader pk_invheader 2011-05-20 15:50:07.210 3713 InvHeader uix_invheader 2011-05-19 19:15:01.370 371 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.
"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
|
|
|
|
|
Valued Member
      
Group: General Forum Members
Last Login: Friday, April 05, 2013 5:29 AM
Points: 58,
Visits: 262
|
|
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
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Wednesday, November 28, 2012 9:36 PM
Points: 4,
Visits: 51
|
|
Thanks! You're correct.
I should have thought of timestamp/rowversion instead of datetime.
|
|
|
|