SEO

#1 (**permalink**) 02-29-2008, 06:51 AM

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?

#2 (**permalink**) 02-29-2008, 06:51 AM

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

#3 (**permalink**) 02-29-2008, 06:51 AM

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

Madhivanan

#4 (**permalink**) 02-29-2008, 06:51 AM

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.

#5 (**permalink**) 02-29-2008, 06:51 AM

<paulshealy1@gmail.com> wrote in message
news:1112105115.205877.260460@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

#6 (**permalink**) 02-29-2008, 06:51 AM

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@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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

top keyword and sorting