Lynn Pettis (11/27/2008)
Yep, the code you wrote works about the way you say it will, but I figured out the problem in your code in the second block. First, I wouldn't write a query using the NOLOCK hint unless absolutely necessary. Take your code in the second code block, and change your last query to use the query hint (index = 1) IN PLACE OF the (nolock) you have. Better yet, copy the query and run both (one with nolock and one with index = 1). You will see that the one with (index = 1) returns an ordered set.Edit: And just for s and g's, I ran it also with no hint on the table with the clustered primary key, still returned an ordered result set.
Another Edit: Added a query using both hints (nolock, index = 1), returned an ordered result set.
Hi Lynn,
My bad. I blundered when I wrote the code; I intended to use the locking hint Jeff recommends in his article, which is TABLOCKX.
I just tested with both "WITH (TABLOCKX)" and "WITH (TABLOCKX, INDEX = 1)", and I got an unordered result in both cases.
As I said to Jacob in my previous reply, both NOLOCK and TABLOCK allow an index scan to be driven from the IAM pages instead of the NxtPage pointers. Row- and pagelocking do not allow this in the current version (I tested on SQL Server 2005, SP2). However, I see no reason why Microosoft could not choose to implement some other mechanism to prevent duplicated or missed rows when using an index scan with row or page locking from the IAM pages - after all, they have already implemented such a mechanism for heaps. (Google "table scan from hell" for more info). I did not test this behaviour on SQL Server 2008 yet - for all I know, it may lareaby have been changed!! :w00t: