SQL Server – Non-Clustered Index and Row Order Guarantees

clustered-indexindexnonclustered-indexsql server

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.

Best Answer

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:

Related Question