Microsoft KB Archive/917040

= How to use the timestamp column of a table for optimistic concurrency control in SQL Server 2005 =

Article ID: 917040

Article Last Modified on 11/20/2007

-

APPLIES TO


 * Microsoft SQL Server 2005 Express Edition
 * Microsoft SQL Server 2005 Standard Edition
 * Microsoft SQL Server 2005 Workgroup Edition
 * Microsoft SQL Server 2005 Developer Edition
 * Microsoft SQL Server 2005 Enterprise Edition
 * Microsoft SQL Server 2005 Standard X64 Edition
 * Microsoft SQL Server 2005 Enterprise X64 Edition
 * Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
 * Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems

-





INTRODUCTION
The timestamp column of a table can be used to determine whether any value in the table row has changed since the last time the table was read. This article describes a way to use the timestamp column of a table for optimistic concurrency control in Microsoft SQL Server 2005.



MORE INFORMATION
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 Note  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:

http://msdn2.microsoft.com/en-us/library/ms182776.aspx

Keywords: kbhowto kbinfo kbexpertiseadvanced kbsql2005engine KB917040

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.