#sql2012 ora_rowscn rowversion

Well, for whatever reason, our application instead uses the ORA_ROWSCN function to return the System Change Number of the last transaction to touch each row.  To make this work at a row level, one must create the table with the ROWDEPENDENCIES option.

In searching for a comparable feature on SQL Server 2012, I found the datatype ROWVERSION. A column of ROWVERSION datatype will get a new, sequentially increasing value when inserted and on any update.  The value will be unique to that row, so multiple rows affected by one INSERT or UPDATE statement will get unique values.  While somewhat different than ORA_ROWSCN, keeping the MAX(ROWVERSION_COLUMN) from one query and using it on the next refresh to find rows with ROWVERSION_COLUMN > that value provides an effective replacement.  Additionally, columns of ROWVERSION can be indexed, making the query fast.

To make the column's purpose obvious to my developers, I've chosen to name the column ORA_ROWSCN in any table where this capability is needed.  No, a SQL Server developer won't get it, but my team of Oracle folks will.

Here's an example:

CREATE TABLE foo (   foo_key int identity(1,1),   foo_value varchar(128),   ora_rowscn rowversion ) GO

INSERT INTO foo (foo_value) SELECT o.name FROM sys.objects o GO SELECT * FROM foo GO

Notice that each row has a unique value for ORA_ROWSCN.  Now let's update a few rows.

UPDATE foo SET foo_value = foo_value WHERE foo_key <= 3 GO SELECT * FROM foo ORDER BY foo_key GO

Notice that the first 3 rows have new values for ORA_ROWSCN.  Let's make it more interesting by exercising the use case described at the start.

DECLARE @max rowversion

SELECT @max = MAX(ora_rowscn) FROM foo

UPDATE foo SET foo_value = foo_value WHERE foo_key BETWEEN 10 and 20

SELECT * FROM foo WHERE ora_rowscn > @max ORDER BY foo_key GO