ugo boy (2/20/2013)
Thanks Gail for your response. does it mean that a previous query can distort the indexed order?
Err, no. Selects don't change indexes.
When I ran the query I was talking about, I used a (nolock) hint and that was when the results came back in the unsorted order but when I reran the query without the (nolock) hint, the results came back in the correct ascending order of the clustered indexed primary key.
does a (nolock) hint affect sort orders, can it?
Again, without an ORDER BY on your query, there's no such thing as 'correct order'. Without an Order By, you're telling SQL that there is no meaning, no importance, no relevance to the order of the data, that any order is as good as any other order.
If you need a specific order, you *must* put an ORDER BY on the query.
btw, on that nolock, see - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability