471,071 Members | 1,209 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,071 software developers and data experts.

Query RowVersion in SQL Server 2005

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

Feb 6 '07 #1
1 4273
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
Feb 6 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by brendan_gallagher_2001 | last post: by
7 posts views Thread by angelasg | last post: by
3 posts views Thread by alvinstraight38 | last post: by
2 posts views Thread by Luke.Schollmeyer | last post: by
3 posts views Thread by rcamarda | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.