• ranjitrjha (10/22/2009)


    GSquared,

    Your explanation makes sense but when i am running

    select top 10 * from table1

    select top 10 * from table1

    then for both queries, result set is same.

    It's only when I put a nolock hint as in queries below, the result set differs.

    select top 10 * from table1

    select top 10 * from table1 with (nolock)

    Assuming that a clustered index exists on table1:

    The first query is likely to produce a query plan which forward scans the clustered index and therefore produces results in logical clustered index order (since it scans the linked list of clustered index pages). This behaviour isn't guaranteed as such, but has always behaved this way, at least as far as I recall. The decision about how to return data to satisfy the query is made by the Storage Engine.

    With NOLOCK (or READUNCOMMITTED as I much prefer it), the Storage Engine might choose to perform an IAM-ordered scan rather than following the clustered index chain. From memory, I think this is true if table1 is at least 64 pages in size. Using an IAM scan, data will be returned in the order pages were allocated. The same effect occurs if you specify TABLOCK - the Storage Engine can use the (potentially more efficient) IAM scan if it can guarantee that row movement from things like page splits won't screw things up. With READUNCOMMITTED/NOLOCK you are saying that you don't care about consistency, so the Storage Engine may choose the IAM scan. With TABLOCK, it knows that there can't be any concurrent modifications, so again an IAM scan is safe.

    The Storage Engine always uses the IAM method when scanning a heap (though obviously not for any non-clustered indexes!) so that's why I qualified the above to restrict it to the case where table1 is not a heap.

    Paul