I have a developer that would like, when doing a select statement with no order by, the rows in a table to be in the order they were inserted. The developer suggested changing from a clustered to non-clustered index.

By changing the index from clustered to non-clustered, does this make any guarantees about the order in which rows would appear in the table?

This question is mostly for my curiosity; I am going to suggest using an identity column instead, but this request got me thinking. Timestamp could be used but there is a chance rows can be inserted simultaneously.

Thanks in advance for your help.


3 Answers 3


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:


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.