It can be very complicated. When you query against any dynamic table (one
that is being constantly updated), there is always a chance that the data
will change in the course of the SELECT. The rows being selected are not
determined until the query engine finds them--row-by-row. Once the internal
cache-size rows (about 4K) are found during the SELECT operation, the client
application is notified and the search (SELECT) process stops. Once the
client empties the cache (as with DataReader.Read) new rows are located and
cached. During this process fetched rows (rows already sent to the client)
could have been deleted, new rows could have been added (in the range of
rows already sent or not yet sent to the client), existing rows could have
been changed--those sent, or those not sent. With the disconnected model (as
opposed to server-side cursors) the client gets a snapshot of the "window"
on the rowset state as the SELECT query walks the query plan fetch rowset.
If it's critical that you need a consistent set of rows at a point in time,
then you need to take steps to build a consistent rowset. I doubt if
transactions will help, but it might. The problem with locking down the
server while you fetch rows is that it cripples scalability--other
applications must wait while a specific client fetches a rowset. I would be
hesitant to endorse this approach. Most reports that require this behavior
but use parallel database snapshots made at specific times during the day.
Another approach is to SELECT into a #temp table that is not subject to
changes. The new database snapshot feature in SQL Server 2005 helps here
too. This is another reason why it does not make sense to do a COUNT(*) to
determine the number of rows that will be fetched with a SELECT--an instant
later when the rows are actually fetched, the count would be off.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit
www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
Miha Markic said:
Depends on default implicit transaction defined by database on what
records you get.
Anyway, what exactly are "self-consistent" records?
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development
www.rthand.com
Blog:
http://cs.rthand.com/blogs/blog_with_righthand/
David Thielen said:
I guess my question is if there is no transaction, will the ResultSet come
back self-consistent? I know a split-second later it could be wrong as an
insert or update can hit the table but that is also true with a
transaction
after I call Commit after the select.
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars -
http://www.windwardreports.com/film.htm
:
It depends on your requirements but usually there is no need for
(explicit)
transaction.
--
Miha Markic [MVP C#, INETA Country Leader for Slovenia]
RightHand .NET consulting & development
www.rthand.com
Blog:
http://cs.rthand.com/blogs/blog_with_righthand/
1) that will always return 0 or 1 rows?
2) that will retun N rows?
In each case where the results will be displayed to the user and so
there
can be changes that occur to the DB after the select completes.
--
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars -
http://www.windwardreports.com/film.htm
