7

I am getting the rows in different order when I use 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

in my stored procedure.

Below is the query defined in the stored procedure.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT   CaseRateDetailId,AmtPerWeek
FROM    CaseRateDetails
WHERE   CaseRateInfoId = @CaseRateInfoId

It returns AmtPerWeek like this:

10000,15000,5000,20000,25000,..

When I run the same query without using 

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

statement it returns the rows in the correct order i.e. 5000,10000,15000,20000,25000,....

I can use the order by AmtPerWeek clause in above query but I want to know the reason why it is behaving like this? Why it is changing the order of rows?

Alexandre
  • 4,382
  • 2
  • 23
  • 33
Diya Khan
  • 201
  • 2
  • 10

1 Answers1

10

Under NOLOCK or TABLOCK you can get an allocation ordered scan which reads the pages in file order rather than following the leaf level of an index.

It doesn't show up in the execution plan whether or not it uses this method. Without ORDER BY no order is guaranteed.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • That's fascinating - you are right it doesn't show up in showplan. Deleted my answer since you have proved it was wrong. – Ben Feb 02 '12 at 11:52
  • 1
    @Ben - When the plan shows an index scan with `Ordered:False` the relational engine indicates it doesn't care what order the rows are returned in, which means that the storage engine will consider this as a preferable option for indexes > 64 pages and where the data cannot change (tablock) or the isolation level is such that it prefers speed to any guarantee of consistency. Allocation ordered scans are much more likely to miss rows or read rows twice under conditions of concurrent data modifications than an index ordered scan. – Martin Smith Feb 02 '12 at 12:21