1

I would like to know what is the purpose of the TimeStamp datatype in SQL Server.

As it's binary, it's not human readable and I cannot convert to datetime or any other type. As we don't use any kind of replication it makes no sense to me.

The reason I ask is that I inherited a database where most of the tables have a TimeStamp column and I'm not able to do an 

INSERT INTO SELECT *

or 

SELECT * INTO

OK, I never do this on procedures or any production scenario, but I do a lot on development to create clones of a table or copy smaller dataset into temp tables.

Is it safe to drop these columns? They are not queried anywhere in the system.

Thanks all!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Frederico Almeida
  • 317
  • 4
  • 12
  • I think [this post](http://stackoverflow.com/questions/4460197/what-is-difference-between-datetime-and-timestamp) should answer your question – Hill May 26 '16 at 16:33
  • We used it for checking if someone else has updated the row since it was fetched from the database – James Z May 26 '16 at 16:35

1 Answers1

3

These are usually used for conflict detection.

Whenever you insert or update a row in the database, a new and unique timestamp value is placed in the timestamp field in the row.

This means that you can detect the following scenario:

  1. You retrieve one or more rows from the database
  2. You stage changes to these rows, in memory
  3. Some other process updates one or more of those rows in the database
  4. You want to update the database, but you now need to handle the conflict, should you re-query the rows that other process changed, etc.

Point 4 can be detected using timestamps. If the timestamp of any of the rows you queried in point 1 has changed, then the rows are no longer what you got in point 1.

Lasse V. Karlsen
  • 380,855
  • 102
  • 628
  • 825
  • There is no way anyone here can tell you whether it will be safe to drop those columns or not. – Lasse V. Karlsen May 26 '16 at 16:35
  • I agree, I think the safest thing to do is specify the columns when needing to do an `INSERT INTO` for dev table. – SQLChao May 26 '16 at 16:40
  • I dropped that field and created a datetime2 field to handle conflict detection. Now I'm doing all possible tests and QA. If I have any drawback I'll let you know. Thanks guys! – Frederico Almeida May 27 '16 at 08:58