473,216 Members | 1,292 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 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 4344
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Bill | last post by:
I'm trying to devise a query for use on SQL Server 2000 that will do what was previously done with one query in MS Access. The MS Access query was like this: SELECT Count(*) as , Abs(Sum( Like...
3
by: brendan_gallagher_2001 | last post by:
Hi, I have a view(A) and I am trying to do a join on another table (B) to include only rows where date values in view A is greater than in table B. I also want the view to pick up rows in viewA...
7
by: angelasg | last post by:
Here is sample data I'm working with: ID ShiftDate SegTime 99 5/2/2005 5/2/2005 1:00:00 PM 99 5/2/2005 5/2/2005 1:04:00 PM 99 5/2/2005 5/2/2005 1:43:00 PM 99 5/2/2005...
0
by: Douglas Osborne | last post by:
I am trying to pull the rowversion down from SQL and place it into my form as a hidden string variable. Then on postback I need to convert the string back to the byte array and see if it is the...
5
by: Bari Allen | last post by:
I'm trying to test for concurrency, using a SQL Stored Procedure on a RowVersion (timestamp) Field. The vb code I'm using is as follows Dim cmd As New SqlCommand("ConcurrencyCheck", cn) Dim...
3
by: alvinstraight38 | last post by:
Hi everyone, I am trying to access a client's SQL query anaylzer to run a SQL script, and I can not find it anywhere. I have not used SQL in a few years, but I would always see a menu in the...
5
by: BenG | last post by:
Hi. I have a gridview control on a web form (asp.net 2.0) that's bound to a objectDataSource. The objectdatasource which is bound to a class I've written in the DAL to read and update the database....
2
by: Luke.Schollmeyer | last post by:
I found an unusual problem between 2000 and 2005 I haven't been able to decipher from any documentation. The query structure is as follows: select * from tableA a join tableB b ON...
3
by: rcamarda | last post by:
Hello, I ran a query that I thought would take an hour, but instead took 14 hours to run. The consequence was it bogged down our data warehouse and the overnight build was adversely impacted. Is...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

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.