TL/DR: If it is important that the rows in a result set are in a certain order, the query MUST include an ORDER BY clause specifying that order, regardless of what indexes exist on the underlying tables.

Hi Ryan,

I'm concerned that maybe the previous answers to your question didn't flesh out the issue enough to keep you from wandering into problematic territory. The relational model defines a relation as an unordered set of tuples. In SQL terminology, a relation is represented as a table (and tuples as rows). The relation:table equivalency is not perfect, however, especially because a relational database has to store actual tables with actual rows instead of conceptual relations comprised of conceptual tuples, so it has to store the rows in some kind of order. A clustered index on the table will govern the logical order in which rows are stored, while tables with no clustered indexes, called heaps, will logically order rows in the order in which they were inserted.

*However*, clustered indexes ONLY provide a logical order for storing rows and a means to seek for rows when the searching on the clustered index key. The existence of a clustered index does NOT in any way guarantee the order in which rows will be retrieved or returned from the table. In SQL Server, the query optimizer generates an execution plan that will be guaranteed to return all rows that meet the search conditions (and only those rows), but the rows will be returned in the order they leave the last operator in the execution plan. Since some execution plans require sorts of intermediate result sets, the final order of the rows may or may not correspond to the order of rows in a clustered index.

Accordingly, if it is important that the rows in a result set are in a certain order, the query MUST include an ORDER BY clause specifying that order, regardless of what indexes exist on the underlying tables. That is the ONLY way to guarantee that rows in a result set are ordered in a specific manner. Some people are led astray by the fact that SQL Server often returns rows from a table in order of the clustered index key even in the absence of an ORDER BY clause, but it is merely coincidence that the rows are in that order when they leave the final operator in the execution plan. It can't be said too often - the only way to guarantee the order of rows in a result set is to include an ORDER BY clause in the query.

Here endeth the lesson. 😀

Jason