Why "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED" returns rows in different order?

Question

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?

Answer 1

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.