Query RowVersion in SQL Server 2005

[Guedes]

Hello all,
I am new at SQL Server 2005 and have been reading everything I canfind
about the new optimistic concurrency control mechanisms. As far as I
can tell, the Snapshot Isolation Level is based avoids the use of
shared locks using rowversioning instead.

To control rowversions in SQL Server 2000 I was using an extra column
in each table containing a rowversion datatype. What i do with this is
find out if a row was modified. Is it possible in SQL Server 2005 to
avoid the use of this extra column, using the new versioning features?

What I mean is, if the Snapshot Isolation already manages rowversions
in TempDB, is it possible for me to query those versions using some
stored procedures or functions given by SQL Server 2005 system? What
functionalities does SQL Server 2005 gives me for that (any
documentation pointer would help)?

This would allow me to do the exact same processing that i was doing
with the rowversion column in SQL Server 2000 without the need for
that extra column.

For all of you my thanks and best wishes

Susana Guedes

[Erland Sommarskog]

Guedes (su**********@gmail.com) writes:

I am new at SQL Server 2005 and have been reading everything I canfind
about the new optimistic concurrency control mechanisms. As far as I
can tell, the Snapshot Isolation Level is based avoids the use of
shared locks using rowversioning instead.

To control rowversions in SQL Server 2000 I was using an extra column
in each table containing a rowversion datatype. What i do with this is
find out if a row was modified. Is it possible in SQL Server 2005 to
avoid the use of this extra column, using the new versioning features?

I don't think so. That is, there may be a way to do it, but it would
require far more work than that timestamp/rowversion column.

Snapshot isolation addresses a different issue than a rowversion column.
The rowversion is for optimistic locking: "let's hope the row does not
get modified while we are looking at it."

Snapshot isolation is rather about consistency. I want to know the
state of matters in this precise now. So it is actually a more strict
isolation level than the default READ COMMITTED.

It is also possible to configure a database so that READ COMMITTED is
implemented through the snapshot mechanism, but this works differently
from pure Snapshot isolation. In pure Snapshot Isolation, you don't
want to see wnything that happened after your transaction started,
not even if has been committed, which you accept with Read Committed
Snapshot.

It's nevertheless possible to do optimistic concurrency without
rowversion: you simply compare all columns. Several client APIs
employ this method. In my opinion, it's far less elegant.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx