THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

DISTINCT and ORDER BY

Using DISTINCT is another one of those examples where people's experiences don't always match the truth. Using DISTINCT without an ORDER BY probably returns the results in order. If your experience tells you that your results are ordered whenever you use DISTINCT without an ORDER BY, don't trust your experience. Just like I explained in my recent posts on UNION ALL and TOP .. ORDER queries in views, you must have an ORDER BY to guarantee ordered results.

If you use DISTINCT and examine your actual execution plan, you may see that a sort was done. Just because a sort was done internally doesn't mean that you are guaranteed perfectly ordered results. You'll see ordered results virtually every time you use DISTINCT without an ORDER BY. But if you really want ordered results absolutely positively each and every time, you must use an ORDER BY. I have seen real world queries that have a DISTINCT without an ORDER BY fail to return everything in order. Again, if order matters, you must specify ORDER BY.

Quoting from the documentation:

"The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified."

Although the second and third execution plans appear identical, only the third query with the DISTINCT is guaranteed to return ordered results.

create

table #dupes (a int);

insert into #dupes (a) values (2);
insert into #dupes (a) values (2);
insert into #dupes (a) values (1);
insert into #dupes (a) values (1);

select          a from #dupes;

select distinct a from #dupes;

select distinct a from #dupes order by a;

ExecutionPlanOrderBy

Figure 1. Although the query plans for Query 2 and Query 3 appear identical and they both show a sort, only Query 3 is always certain to return ordered results because it has ORDER BY.

This is my third post about using ORDER BY. There is one case I've seen where ORDER BY doesn't make sense and shouldn't be used. Tables have no sense of order, so there is no point in using ORDER BY on an INSERT SELECT statement.

Published Sunday, March 10, 2013 3:40 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solution Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement