DISTINCT removes duplicate result rows without guaranteeing their order. My original claim that it always sorts output was incorrect.
DECLARE @T table (ID int);
INSERT @T VALUES (3),(1),(2),(3),(1);
SELECT DISTINCT ID FROM @T; -- No guaranteed order.
SELECT DISTINCT ID FROM @T ORDER BY ID; -- Explicit ascending order.
SQL Server can use a sort, stream aggregate, hash aggregate or another suitable plan. Sorted output from one run is not guaranteed. Specify ORDER BY in the outer query when sequence matters.
SELECT DISTINCT imposes SELECT-list rules on ORDER BY expressions. That does not make ORDER BY redundant. Sorting can consume resources. An existing access path can also provide the needed order.
Use a defined order with TOP when business rules require specific first or highest rows. Compare plans and measurements for performance. Keep the ORDER BY that defines the intended result.
Related reading
- SQL SERVER – TOP and DISTINCT – Epic Confusion
- SQL SERVER – Performance Comparison – BETWEEN, IN and Operators
- SQL – Difference between != and <> Operator used for NOT EQUAL TO Operation
- Are Not Equal to Operators Equal to Not In? – SQL in Sixty Seconds #102
- SQL SERVER – Compound Assignment Operators – A Simple Example
- SQL Puzzle – A Quick Fun with BitWise Operator
- The Filter Operator: When SQL Server Reads Everything and Keeps a Few Rows
- Pinal Dave on YouTube
Observed sorting is not an ordering contract, it is a plan outcome unless ORDER BY specifies the required sequence.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
2 Comments. Leave new
Good morning, Pinal. I don’t believe that SQL Server guarantees ordering of the results if the DISTINCT keyword is used without a corresponding ORDER BY clause. If the optimizer elects to use a stream aggregate for the DISTINCT, then the results will be ordered. However, if the optimizer uses a hash aggregate for the DISTINCT, the results will be unordered. The presence of a nonclustered index on the table with the leading key column matching the column from the DISTINCT clause will encourage the optimizer to select a stream aggregate, because the values are already sorted.
Hi Bryan, Yes, you are absolutely correct.