timestamp columns in SQL server are misunderstood. I know they are because today I was definitely misunderstanding them, and I'm (according to Google) not the first.

So what's to not understand? Well, here's the deal. I had an application that required me to log for each row in a specific table the exact time at which it was last updated/Inserted. “Great!” I thought, “Sounds like a task for the timestamp column!” . And so we tried.

The funny thing about a timestamp column is that when we wanted to retrieve it from the database in the unit test, we had to Cast it into a datetime DB type, since it is Binary. That should have been my first clue, but I kept on. When we finally got the data back, the year of the date column was always set to 1900. Yep. More inserts, more 1900. Each row was different value in a way, waay back at the end of the number, but the date was absolutely not what I expected.

So what was the problem? The problem was that we were using timestamp for something it wasn't made for. A timestamp is there simply for the purpose of keeping track on the “version” of one unique row in a database table. It is a totally binary number that changes, but it does not represent a datetime construct. The only thing promised is that whenever a row is inserted/updated, that the value in that column will stay unique across changes of the specific row. that's it. Since it was not a datetime construct, casting it into a datetime type resulted in how the datetime casting engine parsed this binary values, defaulting to 1900 because there really was no year specified in the converted value.

So now that its established that we do not want to use a timestamp, how do I keep track of a row's “last updated” state? The hard way, that's how. I can either get the date as part of a stored procedure parameter and set it every update, or I can just set it on updates and inserts automatically inside the stored procedure using “GETDATE()” functionality in SQL. There's another way: doing a trigger on Update and Insert to set the column value to the current date, but triggers are something I try to avoid, so let's ignore that possibility.