TimeStamp VS DateTime data types in SQL SERVER
Couple years ago I was struggling a bit with timestamp (Also known as ROWVERSION) and DateTime data types in Sql Server as I wanted to have the date and time of when each record in Table is modified in my application. Yesterday I saw couple of posts in the newsgroups in this regards so I decided to explain it again here for those who have the same issue.
If you want to have the last modified date and time of each record then you have two options:
1) Define the field as datetime in your database and then in your stored procedure use something like this:
CREATE PROC NWtest
@endDate datetime = NULL
AS
IF @enddate IS NULL
SET @endDate = GetDate()
SELECT OrderDate
FROM Northwind.dbo.Orders
WHERE OrderDate<@enddate
Order By OrderDate
GO
The reason is that the default in storedproc must be a constant or NULL
2) Open the table in Design mode in enterprise manager(Or much better, use ALTER TABLE in Query Analyzer), find the column, and in the “Default Value” enter (GETDATE()).Now you won’t have to worry about populating that column in any procedures Because SQL Server will put in the current date and time when the record is
inserted.
TIMESTAMP is an unfortunate misnomer, as the data stored here really has nothing to do with TIME at all. ROWVERSION is an equivalent data type and is the syntax I recommend when using to track changes to a row, if for nothing else, to avoid this very ambiguity. Also, I heard that in some future version of SQL Server, the TIMESTAMP designation will go away.You can see these articles:
http://www.aspfaq.com/2448
http://www.aspfaq.com/2499
Having said this ,I thought that when do people ever use timestamp – I mean, why was it ever included. I understand it to be an 8 byte binary number that changes when a record changes, and is unique within the scope of a database, but I don’t see the benifits of using it. It doesn’t provide an audit trail of row data as far as I know, and is not a candidate for PK as it changes over time.What benifits are there for using it?. I put my newbie hat on and I did some goole searches 😉
Answer:
Optimistic concurrency ( Simple ,isn’t it? :-)).When you read a row to present to a user, read and store the value in the timestamp column, When you later issue the UPDATE:
UPDATE tbl
SET …
WHERE pkCol = Your_Primary_Key_Value
AND tsCol = OriginalTimeStampValue
Then check number of rows modified by the UPDATE. If 0, the row was either deleted or changed or updated by somebody else while you were looking at it.
