9

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.

  • A non clustered index has no rows, it simply contains the indexed columns with pointers to the rows in the clustered index. These pointers to the data contains the primary key value.. A clustered index is the table thus it contains the rows. Dropping a clustered index on a table results in a heap structure which is generally not advised, however it really depends. – Namphibian May 9 '18 at 22:19
29

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:

  • Thanks for the answer. That article addresses my question exactly. – 630 May 9 '18 at 13:20
17

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

  • Ah, just what I suspected. This is excellent, thank you. – 630 May 9 '18 at 13:21
6

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, privacy policy and cookie policy

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