Query performance with order by clause?

[M Wells]

Hi all,

Just wondering if anyone can tell me if an order by clause on a select
query would have any impact on the time it takes to retrieve results?

Essentially I'm selecting Top 1 out of a table via various criteria
and currently getting it back without an order by clause. The order by
would only include the column that has the clustered primary index on
it.

Can anyone tell me if in theory this will slow the query down?

Many thanks in advance!

Much warmth,

Murrau

[Erland Sommarskog]

M Wells (pl**********@planetthoughtful.org) writes:

Just wondering if anyone can tell me if an order by clause on a select
query would have any impact on the time it takes to retrieve results?

Essentially I'm selecting Top 1 out of a table via various criteria
and currently getting it back without an order by clause. The order by
would only include the column that has the clustered primary index on
it.

Can anyone tell me if in theory this will slow the query down?

For most situations this is an uninteresting question. TOP 1 with an
ORDER BY means "give me one row, I don't care which", but it's not good
for a random selection.

So if you need your row to be deterministically selected, then you must
have an ORDER BY clause.

The cost for the ORDER BY clause is likely to be marginal, if the order
by columns agrees with the clustered index.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp