Posted by Damien on 09/12/06 07:15

Erland Sommarskog wrote:
> bozzzza@lycos.co.uk (bozzzza@lycos.co.uk) writes:
> > We have a big table connected to a web server, and I want the number of
> > rows to be limited that get returned.
> >
> > So I could do something like this:
> >
> > select top 10 *
> > from object
> > order by code
> >
> > I then realised looking at the help file, because the "order by" clause
> > is set the SQL Server has to build the complete dataset, do the order
> > by and then filter it to the first ten rows.
>
> Logically, it would that yes. But if there is an index on Code, SQL Server
> would use that index, why this could be a fast operation.
>
> > So I was wondering if I put the query into a view like so :
> >
> > create view vw_object as
> > select top 10 *
> > from object
> >
> > Then ran the query like so:
> >
> > select *
> > from vw_object
> > order by code
> >
> > would the SQL server just get the top 10 rows from the view first, then
> > apply other order by on it afterwards?
>
> Yes. That would mean that SQL Server would first get any 10 rows, and then
> sort the 10 rows on Code. That does not appear as a very useful operation
> to me....
>
Except, of course, that TOP is only valid in a view definition if it
has an ORDER BY clause (this is the opposite of the trap that people
have been falling into recently of thinking that an ORDER BY in a view
definition determines the order of the final result set)

Damien

[Back to original message]