top keyword and sorting

[paulshealy1@gmail.com]

I heard a claim recently at a SQL Server users group meeting that the
TOP keyword forces sorting on a database server. I can't find any
reason this might be true and the Books Online say nothing about it.
Can someone verify this claim one way or the other?

[David Portas]

Re: top keyword and sorting

In production code, TOP is pretty useless in a query without ORDER BY
(unless you actually want unpredictable results - not generally a good
idea). It is ORDER BY that potentially forces a sort on the data,
unless the server can take advantage of an index.

--
David Portas
SQL Server MVP
--

[Madhivanan]

Re: top keyword and sorting


I think Top will not do any sort until order by is specified

Madhivanan

[paulshealy1@gmail.com]

Re: top keyword and sorting

David,

Thanks for the response. Your thinking matches mine. Perhaps this
person was confused because many TOP queries are sorted for some other
reason - e.g., an ORDER BY or a clustered index.

[Simon Hayes]

Re: top keyword and sorting


<paulshealy1@gm ail.com> wrote in message
news:1112105115 .205877.260460@ f14g2000cwb.goo glegroups.com.. .[color=blue]
> David,
>
> Thanks for the response. Your thinking matches mine. Perhaps this
> person was confused because many TOP queries are sorted for some other
> reason - e.g., an ORDER BY or a clustered index.
>[/color]

Be careful - you can't rely on a clustered index to return data in a certain
order; the only guaranteed way to get ordered data is using ORDER BY. This
is because MSSQL may choose some way of optimizing your query which doesn't
return data in the order of the clustered index, so if you need ordered data
you always need ORDER BY.

Simon

[Gert-Jan Strik]

Re: top keyword and sorting

Run the example below, and it is easy to see that the result is not
sorted.

create table #t (col1 varchar(20))
insert into #t values ('a')
insert into #t values ('z')
insert into #t values ('b')
insert into #t values ('y')
insert into #t values ('c')
insert into #t values ('x')

select top 3 * from #t

drop table #t

col1
--------------------
a
z
b

(3 row(s) affected)

Gert-Jan

paulshealy1@gma il.com wrote:[color=blue]
>
> I heard a claim recently at a SQL Server users group meeting that the
> TOP keyword forces sorting on a database server. I can't find any
> reason this might be true and the Books Online say nothing about it.
> Can someone verify this claim one way or the other?[/color]