September 3, 2007 at 4:34 am
Hi,
I have a question about the timestamp type column. In an ETL sytem we want to use this column to decide if rows in a system are updated/new. This approach is choosen:
We decide new/changed columns based on a newer timestamp, we get the highest timestampvalue from the destination db, and retrieve records from the source based on higher values in the timestamp column. Thus we get the new and changed rows. After this the destination rows are deleted for the new and changed records and a work table containing the new and changed records is loaded to the destination table.
I have one big question about this : what happens to the @@dbts when the sql server is restarted? queries the sql engine all tables for the highest value of timestamp columns and bases the new @@dbts on this value or uses it another scheme ? I really have doubts about this timestamp system but my collegue really thinks it will work, please enlighten me 😉
Wim van den Brink
September 3, 2007 at 4:53 am
Wim,
During a server restart SQL Server will still ensure that the new timestamps will be greater than the previous ones. SQL Server persists the timestamp value in its databases' boot pages (one boot page per db). If you restart the server it will check this value, so it it is very fast to look up. If the server crashed, a redo and undo needs to be performed, SQL Server will update the last used timestamp value based on the recovery process. Once again it is fast, since only the redo records are checked.
Regards,
Andras
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply