Posted by Najm on 10/23/07 16:17

On Oct 22, 3:52 am, "bmm" <bmmsletdette...@comlog.dk> wrote:
> I have a Store Procedure on a Sql Server 2000 Where I use the Table Hint
> "NoLock" on all selects.
>
> One of my clients (OleDbConnection from C#) doesn't get the same Result Set
> as the others. The result Set should have 31 rows but this client only gets
> 5!
>
> When I remove all the "NoLocks" everything works fine. How can that be?

Hi there,
I am posting few lines from SQL Sever magazine article written by
Itzik Ben-Gan:

"
With the NOLOCK hint (or setting the isolation level of the session
to READ UNCOMMITTED) you tell SQL Server that you don't
expect consistency, so there are no guarantees.
Bear in mind though that "inconsistent data" does not only mean that
you might see uncommitted changes that were later rolled back, or
data changes in an intermediate state of the transaction. It also
means
that in a simple query that scans all table/index data SQL Server may
lose the scan position, or you might end up getting the same row
twice.

"

You may find the details in the doc: InstantDoc #92888

You can replicate this behavior in SQL Server 2005/2000 using code
given in the above article and I believe it is public.

I hope it helps.

[Back to original message]