This is an excerpt of a blog post by Adam Haines from DEMYSTIFYING SQL SERVER.
This is a quick recap of the post:
- When SQL Server scans a given table it takes a shared lock one row at a time
- If the inserted value occurs before the currently scanned row, the row does not appear in the result set
- When a CLUSTERED index is scanned thus it returns the data in the order of the index sort
- When a heap is scanned it returns data as it finds it on disk, unless an order by is specified
- No sort is ever guaranteed, without an ORDER BY clause
- The direction of the scan impacts which data will be missing and which data is displayed