CODEWITHSUNDEEP
sql-serversql-server-2005
Joda Maki
Joda Maki

Reputation: 5869

Sql Server Implicit Order By in Execution Plan

When I look at the execution plan for a SELECT query, I often notice sorts thrown in there hogging CPU. The problem is that I don't have any order by's in my query, and it looks like the cause of the sorts are ascending/descending order set for the column on the index used in the query.

Why is this happening, and can it be turned off (and should I) ?

Upvotes: 2

Views: 1027

Answers (2)

Michaël
Michaël

Reputation: 6734

The result is sorted using the index automaticaly. You can specify the an order by clause.
It's the purpose of an index, sort data in order to find some lines quickly.

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453328

There are quite a few operations in SQL Server that can cause a sort being added to the plan without an ORDER BY being in the query.

DISTINCT and UNION will cause a sort to get rid of duplicates.

GROUP BY might require a sort on the GROUP BY column if the plan has a stream aggregate rather than a hash aggregate and there is no suitable index that returns the required data pre-sorted.

The segment iterator (used for ranking functions) requires its input sorted on the segment column.

Do you have a particular example?

Upvotes: 3

Related Questions