SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Change Tracking & last modified date
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

malachi151
Posting Yak Master

152 Posts

Posted - 02/01/2013 :  13:29:10  Show Profile  Visit malachi151's Homepage  Reply with Quote
I can see how to identify what has changed using Change Tracking, but is there a way to determine the date of the change?

Basically, all I really need is a date and time of of the most recent change to a record. I had a trigger on the table that did this, but I wanted to replace it with Change Tracking thinking it would have better performance.

Also, my prior trigger checked to make sure that something has actually changed changed on the record prior to updating the time stamp by using a checksum on the row, which made it slower, but insured that if some massive update was run on all records, but didn't actually change any data then the time stamp wouldn't be updated.

I was hoping I'd be able to do this with Change Tracking as well...

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 02/01/2013 :  17:21:05  Show Profile  Reply with Quote
Looks like this functionality is available through "Change Data Capture". Although it doesn't appear to care if a value was updated to the same value - it is transaction based and will capture all transactional changes. Looks like with CDC you can compare values though. I haven't used it yet but looks pretty robust at first glance.

Be One with the Optimizer
TG
Go to Top of Page

malachi151
Posting Yak Master

152 Posts

Posted - 02/06/2013 :  14:28:23  Show Profile  Visit malachi151's Homepage  Reply with Quote
So, is there a way to determine date of last change with Change Tracking?

--------------------------------------------------
My blog: http://practicaltsql.net/
Twitter: @RGPSoftware
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 02/06/2013 :  15:03:22  Show Profile  Reply with Quote
If my memory serves me, the LSN only tracks the order/sequence of change not the date/time of the change.
Go to Top of Page

Passer_by
Starting Member

USA
1 Posts

Posted - 03/18/2013 :  14:33:13  Show Profile  Reply with Quote
Check the sys.dm_tran_commit_table's commit_time column. You can join the sys_change_version returned from the CHANGETABLE(CHANGES...) function to the sys.dm_tran_commit_table.commit_ts column.

SELECT ct.*, tct.commit_time
FROM CHANGETABLE(CHANGES <your table here>, <last_sync_version>) CT, sys.dm_tran_commit_table tct
where ct.sys_change_version = tct.commit_ts
go
Go to Top of Page

terredean111
Starting Member

3 Posts

Posted - 02/11/2014 :  23:21:35  Show Profile  Reply with Quote
Hi malachi151,
I have been doing this from a long time. I am in the same tracking company. You can take a look at our s/w or any other help required I will be happy to help you.
Thanks
http://www.labortimetracker.com/features.cfm
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000