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;
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.