SQL Server – Timestamp Column

While this seems like a simple thing to do, I recently discovered so many ways not to do it (also known as failing) that I just had to document this one.

While this seems like a simple thing to do, I recently discovered so many ways not to do it (also known as failing) that I just had to document this one.

It is very common to want a column of your SQL table to be a timestamp of when the row was written.  At times like these, it's nice to know that there is a "Timestamp" datatype… that does not help at all.  So, how do you do it?

Solution: "datetime" Datatype

The data type must be "datetime" and the Default Value or Binding must be "getdate()".  When I filled in the Default Value or Binding, I used "CURRENT_TIMESTAMP", which was automatically updated to "getdate()", as seen below.

Timestamp Datatype

So what about the "timestamp" datatype?  Microsoft states:

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

In other words, in SQL Server, if you want a timestamp, don't use "timestamp", use "datetime".  If you use the "timestamp" datatype, you'll see data like this in your table: