0

I want to implement incremental ETL on some of my sql server tables, so I need a new column like UpdatedDate to track change dates of my records. I want to transfer only updated and inserted records, so a single column is enough. The question is should I use datetime column and populate it with triggers or it is better to user timestamp column? What are pros and cons?

In both cases I want to create nonclustered index on my columns to find rows from the last sync faster.

  • 1
    Consider using change data capture or change tracking for your need. – Dan Guzman Oct 11 at 11:42
  • 1
    Quote from the manual "The timestamp syntax is deprecated. This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work" – a_horse_with_no_name Oct 11 at 11:49
  • What happens when someone update or delete a row? – McNets Oct 11 at 12:58
  • @McNets There are no deletes, when the row is updated we should update the corresponding row in the target system. – Artashes Khachatryan Oct 11 at 13:59
  • @McNets because the target system has different structure. It is designed for reporting. – Artashes Khachatryan Oct 11 at 14:39
1

Timestamp don't carry any relationship with date and time. Absurdly enough, it is allowed to convert to datetime, but it won't be the date and time that you would hope for. I.e., no relationship with the date and time when the row was inserted/modified. As shown here:

CREATE TABLE #t(c1 timestamp)
INSERT INTO #t DEFAULT VALUES
SELECT c1, CAST(c1 AS datetime) FROM #t

0x00000000000007D5  1900-01-01 00:00:06.683

Since timestamp was such a stupid name for the type, MS decided that we should start calling it rowversion instead. Sound thinking. But the implementation sucked since the name rowversion is treated like an alias to timestamp at parsetime. I.e., specify rowversion in the CREATE TABLE, and when you script it, you'll see timestamp instead.

Wisely enough, MS do not allow this type to be converted to the new date and time types.

So, timestamp/rowversion is out of the question if you want to carry a meaningfule date and time with it. That leaves any of the datetime types, and make sure it is populated. Or use CDC or CT as suggested by Dan Guzman.

  • 1
    But if I use timestamp column, I will still be able to find the rows that have not been synced yet. I need just to select rows with timestamp greater that last sync timestamp. – Artashes Khachatryan Oct 11 at 15:36
  • Yes, that is correct. – Tibor Karaszi Oct 12 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.