We are getting ready to move to the current version of the CSLA framework, and after reading the book, I like how the stored procedures check for new versions of the data. All our tables incorporate both a ModifiedDate and CreatedDate column, of type datetime.
My question is is there any drawback to using just a datetime column as opposed to a timestamp column?
In SQL Server, you cannot explicitly update the TIMESTAMP column. Its value is automatically updated when there is any updates happened on the row.
I am having my update stored procedure looks like the following:
CREATE PROCEDURE dbo.UpdateCustomer
@CustomerID BIGINT,
@RowVersion TIMESTAMP,
@CustomerName VARCHAR(100)
AS
UPDATE dbo.Customers SET CustomerName = @CustomerName
WHERE CustomerID = @CustomerID AND [RowVersion] = @RowVersion
GO
After invoking the stored procedure, the application code checks for number of rows affected.
Regards,
William
Also remember that datetime columns are imprecise (+- 3 ms) and get rounded by the server. That means that what you write is not what you will read back and that you will have hard to reproduce concurrency issues (lots of things can happen in 3ms on a server).
Timestamps do not have these problems but also have drawbacks. First, it is a meaningless binary that can't tell you when the update occured. Second, it it totally propriatary and your system will never be ported and can't play nicely with other systems.
We do audit on all tables with the usual UpdatedBy and UpdatedDateTime columns but for concurrency issues our record version is a simple integer. This means the data access layer does [...] UPDATE MyTable SET [...] RecordVersion = RecordVersion + 1 WHERE RecordVersion = @RecordVersion and PKEY = @PKEY.
You know how many versions of a row where audited without going into the audit tables and you can prevent unauthorized updates easily by rolling back all updates that do not increment RecordVersion.
Both timestamps and integer versionning methods work. Pick the one that matches your needs.
L
Copyright (c) Marimer LLC