470,594 Members | 1,499 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,594 developers. It's quick & easy.

top keyword and sorting

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?

Jul 23 '05 #1
5 3023
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
--

Jul 23 '05 #2

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

Madhivanan

Jul 23 '05 #3
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.

Jul 23 '05 #4

<pa*********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
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.


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
Jul 23 '05 #5
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

pa*********@gmail.com wrote:

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?

Jul 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by dont bother | last post: by
7 posts views Thread by Federico G. Babelis | last post: by
19 posts views Thread by Owen T. Soroke | last post: by
4 posts views Thread by Ambica Jain | last post: by
7 posts views Thread by Kamal | last post: by
20 posts views Thread by Jasper | last post: by
5 posts views Thread by jrod11 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.