Re: CLUSTERD INDEXES

From: Delbert Glass (delbert_at_noincoming.com)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 12:02:16 -0600

>Specifying an ORDER BY clause on the clustered index will not cause the
>optimizer to include a Sort operator in the execution plan

True:
[ ] always
[x] sometimes
[ ] never

That "sometimes" even applies whenever you add the qualification that the
ORDER BY clause and the clustered index both specify the same order.

Bye,
Delbert Glass

"Jacco Schalkwijk" <NOSPAMjaccos@eurostop.co.uk> wrote in message
news:uoB6fZV$DHA.3500@tk2msftngp13.phx.gbl...
> Hi Abraham,
>
> Specifying an ORDER BY clause on the clustered index will not cause the
> optimizer to include a Sort operator in the execution plan, but it will
> change the way the rows are retrieved. If you run the following in Query
> Analyzer:
> USE Northwind
> GO
> SELECT orderid FROM orders
> GO
> SELECT orderid FROM orders
> ORDER BY orderid
>
> You will see that a different index is used to retrieve the rows, and that
> when you look closely at the index scan for the second statement, it
> includes the ORDERED FORWARD clause.
>
> As has already been said in other replies, if you don't specify with an
> ORDER BY clause that you want to have results returned in a certain order,
> the Query Optimizer will return the results in the quickest way possible
> (that's why it is called an Optimizer :-)), and that is far from
guaranteed
> to be in any order at all.
>
> --
> Jacco Schalkwijk
> SQL Server MVP
>
>
> "Abraham" <binu_ca@yahoo.com> wrote in message
> news:eTGes8U$DHA.1956@TK2MSFTNGP10.phx.gbl...
> > Hi,
> > I have a question about CLUSTERED INDEX. When we use ORDER BY in a
> clustered
> > index column , we found out that optimizer skips the sorting since it
get
> > the records in sorted manner.
> >
> > My question is that on multi-processor server does the SELECT return the
> > rows in the CLUSTERED INDEX order, if I do not include 'ORDER BY'
clause?
> > Thanks,
> > Binu
> >
> >
>
>

Relevant Pages

  • Re: CLUSTERD INDEXES
    ... Specifying an ORDER BY clause on the clustered index will not cause the ... the Query Optimizer will return the results in the quickest way possible ...
    (microsoft.public.sqlserver.programming)
  • Re: Problems when using TOP statement
    ... changed execution plan which the optimizer decides to choose when you add ... another clause like TOP to an existing query. ... In some cases adding TOP clause might include a startup filter to the ...
    (microsoft.public.sqlserver.programming)
  • Re: Index chosen is wrong index
    ... some of the other columns in your predicates are part of a clustered index. ... the optimizer might choose a scan of narrow non-clustered ... > SELECT SDKCOO, SDDOCO, SDDCTO, SDLNID, SDSFXO, SDMCU, ... > SDLOTN, SDDSC1, SDDSC2, SDLNTY, SDNXTR, SDLTTR, SDEMCU, ...
    (microsoft.public.sqlserver.server)
  • Re: Question about indexes
    ... clustered index on this particular table. ... >>OfficeNum for the same reason. ... >>SessionID because it is part of the GROUP BY clause. ... > column used by the table in that query is in the index. ...
    (microsoft.public.sqlserver.programming)
  • Re: CLUSTERD INDEXES
    ... > The clustered index is about the organization of the rows in storage. ... > The ORDER BY clause is about the organization of the rows in a result set. ... > The organization of the rows in storage does not always guarantee ... > and if they can be meet the query planner will consider taking advantage ...
    (microsoft.public.sqlserver.programming)
Loading