In SQL Server 2008, using distinct clause is always doing an implicit order by or I need to specify an order by for that? I want to be sure that using distinct put data in order.
Here you have an example, distinct is doing order by
create table #MyTable (id int)
insert into #MyTable values (3)
insert into #MyTable values (2)
insert into #MyTable values (8)
select distinct id from #MyTable
Although the typical implementation of distinct is done using some kind of ordered data structure, the order it uses may not be the one you need.
There are:
The distinct clause does not imply ordering. As such, if you need the data ordered in a particular manner, you have to add an order by clause to the query.
Also note that one of the data structures that can be used is a hashtable/hashset, and though these may produce data that looks ordered if there are only a few values placed into them, with larger quantities this will break down, and regardless, this is implementation specific and undocumented. Do not rely on any such behavior.
DISTINCT clause has nothing to do with ordering records. You have to explicitly use ORDER BY clause for sorting.
select distinct id
from #MyTable
Order By id
DISTINCTin which case the results will almost certainly not be in the desired order. e.g.CREATE TABLE T(X INT);INSERT INTO T SELECT number%2 FROM master..spt_values;SELECT DISTINCT X FROM Tgives results0,1,-1for me.