Does a non-clustered index make any guarantees about row order?
If you need order in your query results, put in an ORDER BY
. It’s that simple.
Check out this article from SQL Server architect Conor Cunningham which pretty much sums this topic up:
- No Seatbelt – Expecting Order without ORDER BY
Absolutely not - without ORDER BY row order is NEVER guaranteed.
The execution plan chosen MAY result in the order wanted, but if so it is by chance.
Storage order vs Result Order DBA.SE thread
Not true, but assume that it was. If the rows are already ordered by the index the cost for order by would be very low, so there is no reason to leave it out.