SQL-Server-2005: Why are results being returned in a different order with(nolock)

i have a primary key clustered index in col1

why when i run the following statements are the results returned in a different order

select * from table

vs

select * from table with(nolock)

the results are also different with tablock

schema:

col1 int not null
col2 varchar (8000开发者_如何学JAVA)

Without any ORDER BY no order of results is guaranteed.

Your question is now heavily truncated but the original version mentioned that you saw different order of result when using nolock as well as tablock.

Both of these locking options allow SQL Server to use an allocation order scan rather than reading along the clustered index data pages in logical order (following pointers along the linked list).

That should not be taken as meaning that the order is guaranteed to be in clustered index order without that as the advanced scanning mechanism, or parallelism for example could both change this.

The order of rows is never guaranteed unless you use an ORDER BY.

If you have to have the rows in a specific order there is no other solution that will return the rows in a predictable order.

If you leave out the order by the DBMS is free to return the rows in any order it thinks is most efficient

Sql Server makes no guarantee about the ordering, it will change based on how Sql Server optimises the query.

To guarantee the order you must use an order by clause.

if you are not specifying an order, it's completely nondeterministic. Today they may be different, tomorrow maybe not.

Supplying a hint may inadvertently guide the query optimizer down a more efficient path.