Very Computer

> Perhaps to just expand a bit on Andrew's reply ...

> The SQL Server query optimizer uses a number of sophisticated algorithms
to
> try to determine an optimal query plan. Many factors influence which
> indexes, if any, the query optimizer will use. The mere existence of an
> index for example does not in any guarantee that SQL Server will choose to
> use that index. Similiarly the mere existence of an ORDER BY clause does
not
> guarantee that SQL Server will use a particular index, although the
> existence of an ORDER BY clause can definitely affect the choice of
indexes.

> So, as Andrew posted, the general answer to your question is, "Yes ... an
> ORDER BY clause **can** affect the index that will be used, but having an
> ORDER BY clause does not guarantee that a particular index will be used."

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.

> > Does using an order by clause affect the index that will be used?

> > If so, can I get poiible improved performance by always using these when
a
> > known index is available?

> > TIA

> > JeffP....

> I hope I wouldn't be wrong in saying that whenever possible I can choose
my
> order by as the column that I have an index on [comma column the index is
> with, ...n].  If performance improves, that's good.

> I was also conerned that the order by would impede performance by trying
to
> satisfy the order by in favor of a better index; what I heard was that
MSSQL
> will use the "best" plan to retrieve the data regardless of the order by.

> JeffP....

> > JeffP,

> > Perhaps to just expand a bit on Andrew's reply ...

> > The SQL Server query optimizer uses a number of sophisticated algorithms
> to
> > try to determine an optimal query plan. Many factors influence which
> > indexes, if any, the query optimizer will use. The mere existence of an
> > index for example does not in any guarantee that SQL Server will choose
to
> > use that index. Similiarly the mere existence of an ORDER BY clause does
> not
> > guarantee that SQL Server will use a particular index, although the
> > existence of an ORDER BY clause can definitely affect the choice of
> indexes.

> > So, as Andrew posted, the general answer to your question is, "Yes ...
an
> > ORDER BY clause **can** affect the index that will be used, but having
an
> > ORDER BY clause does not guarantee that a particular index will be
used."

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.

> > > Does using an order by clause affect the index that will be used?

> > > If so, can I get poiible improved performance by always using these
when
> a
> > > known index is available?

> > > TIA

> > > JeffP....

> I hope I wouldn't be wrong in saying that whenever possible I can choose my
> order by as the column that I have an index on [comma column the index is
> with, ...n].  If performance improves, that's good.

> I was also conerned that the order by would impede performance by trying to
> satisfy the order by in favor of a better index; what I heard was that MSSQL
> will use the "best" plan to retrieve the data regardless of the order by.

> JeffP....

> > JeffP,

> > Perhaps to just expand a bit on Andrew's reply ...

> > The SQL Server query optimizer uses a number of sophisticated algorithms
> to
> > try to determine an optimal query plan. Many factors influence which
> > indexes, if any, the query optimizer will use. The mere existence of an
> > index for example does not in any guarantee that SQL Server will choose to
> > use that index. Similiarly the mere existence of an ORDER BY clause does
> not
> > guarantee that SQL Server will use a particular index, although the
> > existence of an ORDER BY clause can definitely affect the choice of
> indexes.

> > So, as Andrew posted, the general answer to your question is, "Yes ... an
> > ORDER BY clause **can** affect the index that will be used, but having an
> > ORDER BY clause does not guarantee that a particular index will be used."

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> > can be cut and pasted into Query Analyzer is appreciated.

> > > Does using an order by clause affect the index that will be used?

> > > If so, can I get poiible improved performance by always using these when
> a
> > > known index is available?

> > > TIA

> > > JeffP....