NOLOCK hint changes order of records returned

Question

There is a clustered index on a table Client field LastName.

When I simply dump all records from the table, they appear in the alphabetical order unless (nolock) hint is used as in the query in question. That hint changes the order of records. Should it?. I am positive that no other session has an open transaction with the changes to that table (at least sp_who2 is not showing me any).

How can the difference in the order be explained?

Additional information pulled from comments:

1. There is no order by. Should non clustered index enforce the order?

2. The queries still return different order even when using an index hint specifying a clustered index. Should they? I am wondering why nolock changes the order of returned records without a visible change of the plans.

3. I did a WinDiff on them - same except for [the] (nolock) [query hint].

Answer 1

The appearance of an ordered result set, without an ORDER BY clause, often results from a scan retrieving rows in index order. One reason why an index-order scan is generally chosen under the default READ COMMITTED isolation level is that it reduces the chances of unwanted concurrency anomalies such as encountering the same row multiple times, or entirely skipping some rows. This is detailed in several places, including in this series of articles about isolation levels.

With a NOLOCK table hint, this behavior is relaxed, and access to the table is performed under the more tolerant READ UNCOMMITTED isolation level, which may scan data in allocation order instead of index order. As described in that link, the decision about whether to use an allocation-order or index-order scan is left up to the storage engine. This choice may change between executions without a change in query plan.

This may sound very abstract, but can be more easily demonstrated with some queries using undocumented functions against the AdventureWorks2012 database.

USE AdventureWorks2012;
GO
-- Appears to be ordered by BusinessEntityID
-- File:Page:Slot goes up and down several times
-- Show physical locations with sys.fn_PhysLocFormatter (undocumented)
SELECT
    P.BusinessEntityID,
    [(File:Page:Slot)] =
        sys.fn_PhysLocFormatter(%%physloc%%)
FROM Person.Person AS P;

-- Same query with TABLOCK or NOLOCK
-- Allocation-order (IAM) scan
-- Now appears to be ordered by File:Page:Slot instead of BusinessEntityID
SELECT P.BusinessEntityID,
    [(File:Page:Slot)] =
        sys.fn_PhysLocFormatter(%%physloc%%)
FROM Person.Person AS P WITH (NOLOCK);

The queries are borrowed with slight modification from Paul White.

Finally, just to be clear, this answer is about the appearance of an ordered result set. There is no guaranteed presentation order without a top-level ORDER BY.

An allocation-order scan can occur in a variety of other circumstances, such as when a table-level lock is acquired, or the database is in read-only mode. Parallelism can also influence the order the data is returned in. The key point is that without ORDER BY, the order data is returned in can vary over time by design.

Answer 2

When I simply dump all records from the table

...then you should not expect any order. In fact the same query run multiple times could come back in a different order without warning. The reason is that your two queries - which are "different" queries most likely because of the different query text, not because of the hint - have different execution plans (and the difference may be subtle, such as an iterator that looks the same in both plans, but has ordered: true only in one; or a vastly different number of estimated rows because one was compiled before a major data change). It could also be that an allocation order scan is being taken, as James rightly outlined in his answer.

In any case, since you are running a query without ORDER BY, SQL Server infers that you do not care about order, and so goes off and determines the most efficient way to return the rows (it doesn't care about order if you don't).

Let me make this very clear:

If you want or expect a certain order, add an ORDER BY clause

This has come up before:

• Should all queries where you expect a specific order, include an ORDER BY clause?
• After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

And I have blogged about it:

• Bad habits to kick : Relying on undocumented behavior (because where in the documentation do you find any definition of how to expect a "simple table dump" to be ordered?)
• T-SQL Tuesday #56 : SQL Server Assumptions (see #3)

Here is a quote from the latter that reiterates what I said to address your comment about using an index hint instead of an ORDER BY clause:

Even in the case of a hinted index, the index will be used (if possible, else an error in most cases), but just because the index is used doesn't mean the results will be returned sorted by the key(s) in that index. You still need an ORDER BY to ensure sorting by the index key(s).

Conor Cunningham - a pretty smart guy, directly responsible for much of what SQL Server does when it processes a query for you - has also blogged about it here:

• No Seatbelt - Expecting Order without ORDER BY

Please do some reading and then add an ORDER BY clause to your queries before complaining about different or unexpected sorting.

Answer 3

James nicely explained how this works, but I'd just like to reiterate one thing: unless you use an ordering function, the order of rows in the result set is undefined. If you need a given ordering, use an explicit order by clause - if you don't specify it, you're basically saying "I don't care about the order at all", not "Order it by the clustered index".