Why "INSTALL BET INSULATION LEVEL?" Returns rows in a different order?

Question

I get the strings in a different order when I use

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

in my stored procedure.

The following is the request specified in the stored procedure.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT CaseRateDetailId,AmtPerWeek FROM CaseRateDetails WHERE CaseRateInfoId = @CaseRateInfoId 

It returns AmtPerWeek as follows:

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

When I run the same request without using

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

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

I can use the order suggested by AmtPerWeek in the previous query, but I want to know the reason why it behaves like this: Why does it change the order of the lines?

Answer 1

In NOLOCK or TABLOCK you can get an ordered scan distribution that reads pages in file order, not after the index sheet level.

The execution plan does not show whether this method uses this method. Without ORDER BY order is not guaranteed.