With the NOLOCK hint (or setting the isolation level of the session to READ UNCOMMITTED) you tell SQL Server that you don’t expect consistency, so there are no guarantees. Bear in mind though that “inconsistent data” does not only mean that you might see uncommitted changes that were later rolled back, or data changes in an intermediate state of the transaction. It also means that in a simple query that scans all table/index data SQL Server may lose the scan position, or you might end up getting the same row twice.
ref:
Clustered index physically organizes the data in order
(myth/false assumption)
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-i.aspx
Which access methods are available to SQL Server to fully
scan the table’s data when an ORDER BY clause is not
specified
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-ii.aspx
NOLock truth
http://www.sqlmag.com/article/sql-server/quaere-verum-clustered-index-scans-part-iii.aspx
