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, 2019 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"
    – user1822
    Oct 11, 2019 at 11:49
  • What happens when someone update or delete a row?
    – McNets
    Oct 11, 2019 at 12:58
  • @McNets There are no deletes, when the row is updated we should update the corresponding row in the target system. Oct 11, 2019 at 13:59
  • @McNets because the target system has different structure. It is designed for reporting. Oct 11, 2019 at 14:39

1 Answer 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)
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. Oct 11, 2019 at 15:36
  • Yes, that is correct. Oct 12, 2019 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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