Clustered Index and Sorting

Question

select *
from [table_a];

A clustered index sorts the table based on whatever column(s) you choose.

That being said, if I have a clustered index on [column_a], [column_b], and [column_c] and run the same query from above, will the data ALWAYS come back sorted based on that order since that's the order that the clustered index was created on?

More clarification:

If I add an ORDER BY clause on something not in the index, the execution plan will have a sort operator.

If I have an ORDER BY clause on all the columns used in the clustered index, the execution plan will not have a sort operator.

That's why I asked this question in the first place.

Aaron Bertrand · Accepted Answer · 2021-10-03T13:09:34.403

if I have a clustered index on [column_a], [column_b], and [column_c] and run the same query from above, will the data ALWAYS come back sorted based on that order since that's the order that the clustered index was created on?

No.

SQL Server does not guarantee that it will return data in any order unless you specify the order. It is easy to prove things can go wrong by simply creating a covering, non-clustered index that leads on a different column:

Example db<>fiddle

But things can go sideways in other ways, too, e.g. when parallelism or partitioning come into play and SQL Server re-assembles the data from different threads, or when the query gets more complex using joins or filters and a different plan other than a clustered index scan makes sense. Leaving off the order by clause is telling SQL Server: "I don't care about order."

Also, just as a point of clarification:

If I have an ORDER BY clause on all the columns used in the clustered index, the execution plan will not have a sort operator.

...this is true only if the columns are listed in the exact same order as the key definition. ORDER BY c, b, a is "all the columns" but it obviously will produce different output (and require some type of sort operation to get there).

Clustered Index and Sorting

1 Answers1

No.

If you expect and want to be comfortable relying on a certain order, always use an ORDER BY clause.