Does ORDER BY on a clustered key affect performance?

Say I have a table clustered on PrimaryKey, and in all cases I want my results to be ordered by PrimaryKey, so I additionally always ORDER BY PrimaryKey in all queries.

Does this ORDER BY affect performance in any way or is it ignored by the profiler as the rows are already in this order?

I am using a SQL Server 2005 database.

It almost certainly will affect performance.

If you just do a query like

Select *
From Table
Order by PrimaryKey

It likely won't affect anything at all.

Bear in mind, though, that this only determines the order of the rows at the leaf level of the clustered index. If you do JOINs, or use other indexes that avoid key lookups, then the ORDER BY will generate additional work.

If every index and every table that you will ever JOIN or reference when querying this table are all ordered by the same key (in the same direction) then it probably won't impact performance. Outside that very limited scenario, you will see a hit.

Only use ORDER BY when the order of the results actually matters. In my experience it's seldom necessary.

StackExchange Database Administrators Q#10396, answer score: 7