shija03,

If the selectivity for that range is low (to many rows will be selected),
then it could more expensive using the nonclustered index, than scanning the
clustered one or the table (heap). That is why Aaron said that the columns
being referenced in the column list have an impact on which index could be
used. If the nonclustered index is not a covering one for this query, and I
guess it is not because you are requesting all columns and the index key is
just [saved_date] and you did not mention anything about included columns, so
the engine has to use the table or the clustered index if there is one, to
pull the data for the columns not in the index.


AMB


"shija03" wrote:

> On May 15, 1:29 pm, Eric Isaacs <eisa...@gmail.com> wrote:
> > Can you script out and send us the table schema and the script of the
> > indexes on the table? I'm guessing we don't have all the facts yet.
> > The index should speed up the select if it's in the right format. Is
> > that save_date column an INT column or a DATETIME column?
>
> The column saved_date is of type INT
>
> >
> > If the index is in proper format, it will use the index to limit the
> > number of rows, then join the index to the table to get the subset of
> > rows from the table for the SELECT *, unless it's a small table and
> > the optimizer determines that using the index is pointless because the
> > gain wouldn't be enough.
>
> The range of saved_date is 20070101 20080514 - so the subset of
> data that I'm attempting to retrieve is fairly small in comparison to
> the wholesome.
>
> I think I'm going to follow the advice of Aaron Bertrand and choose
> only the few columns that I need for the display.
>