SQL adaptation guide For SQL SERVER 2005, 2008, 2012 / Data dictionary |
SQL Server provides a special type named ROWVERSION, to stamp row modifications. The ROWVERSION data type replaces the old TIMESTAMP column definition. When you define a column with the ROWVERSION, SQL Server will automatically increment the version column when the row is modified. ROWVERSION is just an incrementing number, it does not preserve date or time information. It be used to control concurrent access to the same rows.
DEFINE pv CHAR(16) CREATE TABLE mytab ( k INT, v ROWVERSION, c VARCHAR(10) ) INSERT INTO mytab VALUES ( 1, NULL, 'aaa' ) SELECT v INTO pv FROM mytab WHERE k = 1 UPDATE mytab SET c = 'xxx' WHERE k = 1 AND v =CONVERT(BINARY(8), pv, 2)
With SQL Server 2005, the CONVERT() function does not properly transform the hexadecimal string to a binary value. Therefore, you should only use ROWVERSION as SQL parameter starting with SQL Server 2008. ROWVERSION values can however be fetched with SQL Server versions prior to 2008, for example if you have to define record variables based on the table schema, including the ROWVERSION column.
Since ROWVERSION is a synonym for BINARY(8), ROWVERSION columns cannot be clearly identified in ODBC. Therefore, the following conversion rule applies when fetching data from the server:
When extracting a database schema, ROWVERSION columns are identified as TIMESTAMP columns and can be clearly distinguished from BINARY(N) columns. The fgldbsch tool will produce a CHAR(16)type code in the .sch file for ROWVERSION or TIMESTAMP columns.