|
|
Posted by Damien on 09/12/06 08:25
Damien wrote:
> 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
Brain fart there. Don't know why I was sure TOP wasn't valid without
ORDER BY. Need more caffiene.
Damien
Navigation:
[Reply to this message]
|