You can add a
timestamp column to a table to help maintain the integrity of the database when multiple users are updating rows at the same time. You may also want to know how many rows and which rows were updated without re-querying the table.
For example, assume that you create a table that is named
MyTest. You populate some data in the table by running the following Transact-SQL statements.
CREATE TABLE MyTest (myKey int PRIMARY KEY, myValue int, TS timestamp)
GO
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0)
GO
INSERT INTO MyTest (mykey, myValue) VALUES (2, 0)
GO
You can then use the following sample Transact-SQL statements to implement optimistic concurrency control on the
MyTest table during the update.
DECLARE @t TABLE (myKey int)
UPDATE MyTest SET myValue = 2
OUTPUT inserted.myKey into @t(myKey)
WHERE myKey = 1 and TS = TSValue
IF (SELECT COUNT(*) FROM @t) = 0
BEGIN
RAISERROR ('error changing row with myKey = %d',
16, -- Severity.
1, -- State.
1) -- myKey that was changed
END
is the
timestamp column value for the row that indicates the last time that you read the row. This value must be replaced by the actual
timestamp value. An example of the actual
timestamp value is 0x00000000000007D3.
You can also put the sample Transact-SQL statements into a transaction. By querying the
@t variable in the scope of the transaction, you can retrieve the updated
myKey column of the table without re-querying the
MyTest table.
For more information about the
timestamp column type, visit the following Microsoft Developer Network (MSDN) Web site: