rowversion (Transact-SQL)rowversion (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 否Azure SQL 資料倉儲 否平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

此資料類型會公開在資料庫中自動產生的唯一二進位數字。Is a data type that exposes automatically generated, unique binary numbers within a database. rowversion 通常用來作為版本戳記資料表資料列的機制。rowversion is generally used as a mechanism for version-stamping table rows. 儲存體大小是 8 位元組。The storage size is 8 bytes. rowversion 資料類型只是會遞增的數字,因此不會保留日期或時間。The rowversion data type is just an incrementing number and does not preserve a date or a time. 若要記錄日期或時間,請使用 datetime2 資料類型。To record a date or time, use a datetime2 data type.

備註Remarks

每個資料庫都有一個計數器,會針對在資料庫內包含 rowversion 資料行的資料表所執行的每個插入或更新作業而遞增。Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a rowversion column within the database. 這個計數器是資料庫資料列版本。This counter is the database rowversion. 這會追蹤資料庫內的相對時間,而不是可關聯於時鐘的實際時間。This tracks a relative time within a database, not an actual time that can be associated with a clock. 資料表只能有一個 rowversion 資料行。A table can have only one rowversion column. 每次修改或插入含 rowversion 資料行的資料列時,都會在 rowversion 資料行中插入遞增的資料庫資料列版本值。Every time that a row with a rowversion column is modified or inserted, the incremented database rowversion value is inserted in the rowversion column. 這個屬性會使 rowversion 資料行不適合作為索引鍵 (尤其是主索引鍵) 的候選項。This property makes a rowversion column a poor candidate for keys, especially primary keys. 資料列的任何更新都會變更資料列版本值,因而會變更索引鍵值。Any update made to the row changes the rowversion value and, therefore, changes the key value. 如果資料行在主索引鍵中,舊的索引鍵值便不再有效,參考舊值的外部索引鍵也不再有效。If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. 如果動態資料指標參考資料表,所有更新都會變更資料列在資料指標中的位置。If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. 如果資料行在索引鍵中,資料列的所有更新也會產生索引的更新。If the column is in an index key, all updates to the data row also generate updates of the index. rowversion 值會隨著任何 update 陳述式而遞增,即使沒有變更任何資料列值。The rowversion value is incremented with any update statement, even if no row values are changed. (例如,若資料行值為 5,而 update 陳述式將該值設為 5,即使沒有任何變更,此動作仍然會被視為更新;因此 rowversion 便會遞增。)(For example, if a column value is 5, and an update statement sets the value to 5, this action is considered an update even though there is no change, and the rowversion is incremented.)

timestamprowversion 資料類型的同義字,遵照資料類型同義字的行為。timestamp is the synonym for the rowversion data type and is subject to the behavior of data type synonyms. 在 DDL 陳述式中,請盡可能的使用 rowversion 而非 timestampIn DDL statements, use rowversion instead of timestamp wherever possible. 如需詳細資訊,請參閱資料類型同義字 (Transact-SQL)For more information, see Data Type Synonyms (Transact-SQL).

Transact-SQLTransact-SQL timestamp 資料類型不同於 ISO 標準中所定義的 timestamp 資料類型。The Transact-SQLTransact-SQL timestamp data type is different from the timestamp data type defined in the ISO standard.

注意

timestamp 語法已淘汰。The timestamp syntax is deprecated. 這項功能處於維護模式,並可能在 Microsoft SQL Server 的未來版本中移除。This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. 請避免在新的開發工作中使用這項功能,並規劃修改目前使用這項功能的應用程式。Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

在 CREATE TABLE 或 ALTER TABLE 陳述式中,您不需要指定 timestamp 資料類型的資料行名稱,例如:In a CREATE TABLE or ALTER TABLE statement, you do not have to specify a column name for the timestamp data type, for example:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp);  

如果您沒有指定資料行名稱,SQL Server Database EngineSQL Server Database Engine 會產生 timestamp 資料行名稱;不過,rowversion 同義字不會遵循這個行為。If you do not specify a column name, the SQL Server Database EngineSQL Server Database Engine generates the timestamp column name; however, the rowversion synonym does not follow this behavior. 當您使用 rowversion 時,您必須指定一個資料行名稱,例如:When you use rowversion, you must specify a column name, for example:

CREATE TABLE ExampleTable2 (PriKey int PRIMARY KEY, VerCol rowversion) ;  

注意

您可以使用 SELECT INTO 陳述式來產生重複的 rowversion 值,rowversion 資料行在 SELECT 清單中。Duplicate rowversion values can be generated by using the SELECT INTO statement in which a rowversion column is in the SELECT list. 我們不建議您以這個方式來使用 rowversionWe do not recommend using rowversion in this manner.

不可為 Null 的rowversion 資料行,語意等於 binary(8) 資料行。A nonnullable rowversion column is semantically equivalent to a binary(8) column. 可為 Null 的 rowversion 資料行,語意等於 varbinary(8) 資料行。A nullable rowversion column is semantically equivalent to a varbinary(8) column.

您可以使用資料列的 rowversion 資料行來輕鬆的判斷該資料列自上一次讀取以來是否已執行過 update 陳述式。You can use the rowversion column of a row to easily determine whether the row has had an update statement ran against it since the last time it was read. 若已針對該資料列執行 update 陳述式,則 rowversion 值便會更新。If an update statement is ran against the row, the rowversion value is updated. 若沒有針對該資料列執行 update 陳述式,則 rowversion 值便會與先前讀取時相同。If no update statements are ran against the row, the rowversion value is the same as when it was previously read. 若要傳回資料庫目前的 rowversion 值,請使用 @@DBTSTo return the current rowversion value for a database, use @@DBTS.

您可以將 rowversion 資料行新增至資料表,以確保能在多個使用者同時更新資料列時維護資料庫的完整性。You can add a rowversion 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.

例如,假設您要建立名為 MyTest 的資料表。For example, assume that you create a table named MyTest. 您可透過執行下列 Transact-SQLTransact-SQL 陳述式,在資料表中填入一些資料。You populate some data in the table by running the following Transact-SQLTransact-SQL statements.

CREATE TABLE MyTest (myKey int PRIMARY KEY  
    ,myValue int, RV rowversion);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (1, 0);  
GO   
INSERT INTO MyTest (myKey, myValue) VALUES (2, 0);  
GO  

然後可以使用下列的範例 Transact-SQLTransact-SQL 陳述式,在更新期間將開放式並行存取控制項實作於 MyTest 資料表。You can then use the following sample Transact-SQLTransact-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 RV = myRv;  
IF (SELECT COUNT(*) FROM @t) = 0  
    BEGIN  
        RAISERROR ('error changing row with myKey = %d'  
            ,16 -- Severity.  
            ,1 -- State   
            ,1) -- myKey that was changed   
    END;  

myRv 代表您上次讀取資料列時,該資料列的 rowversion 資料行值。myRv is the rowversion column value for the row that indicates the last time that you read the row. 此值必須由實際的 rowversion 值取代。This value must be replaced by the actual rowversion value. 實際 rowversion 值的範例是 0x00000000000007D3。An example of the actual rowversion value is 0x00000000000007D3.

您也可以將範例 Transact-SQLTransact-SQL 陳述式放入交易中。You can also put the sample Transact-SQLTransact-SQL statements into a transaction. 您可以透過在交易的範圍中查詢 @t 變數來擷取資料表已更新的 myKey 資料行,而不必重新查詢 MyTest 資料表。By querying the @t variable in the scope of the transaction, you can retrieve the updated myKey column of the table without requerying the MyTest table.

下列為使用 timestamp 語法的相同範例:The following is the same example using the timestamp syntax:

CREATE TABLE MyTest2 (myKey int PRIMARY KEY  
    ,myValue int, TS timestamp);  
GO   
INSERT INTO MyTest2 (myKey, myValue) VALUES (1, 0);  
GO   
INSERT INTO MyTest2 (myKey, myValue) VALUES (2, 0);  
GO  
DECLARE @t TABLE (myKey int);  
UPDATE MyTest2  
SET myValue = 2  
    OUTPUT inserted.myKey INTO @t(myKey)   
WHERE myKey = 1   
    AND TS = myTS;  
IF (SELECT COUNT(*) FROM @t) = 0  
    BEGIN  
        RAISERROR ('error changing row with myKey = %d'  
            ,16 -- Severity.  
            ,1 -- State   
            ,1) -- myKey that was changed   
    END;  

另請參閱See also

ALTER TABLE (Transact-SQL)ALTER TABLE (Transact-SQL)
CAST 和 CONVERT (Transact-SQL)CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)
資料類型 (Transact-SQL)Data Types (Transact-SQL)
DECLARE @local_variable (Transact-SQL)DECLARE @local_variable (Transact-SQL)
DELETE (Transact-SQL)DELETE (Transact-SQL)
INSERT (Transact-SQL)INSERT (Transact-SQL)
MIN_ACTIVE_ROWVERSION (Transact-SQL)MIN_ACTIVE_ROWVERSION (Transact-SQL)
SET @local_variable (Transact-SQL)SET @local_variable (Transact-SQL)
UPDATE (Transact-SQL)UPDATE (Transact-SQL)