Sql-server – TIMESTAMP OR DateUpdate

datetimeetlsql servertimestamp

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.

Best Answer

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.

Related Question