THE SQL Server Blog Spot on the Web

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

John Paul Cook


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.


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;


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


No Comments

Leave a Comment


About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in 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 currently studying to be a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement