I have an odd issue -- I have a table ("CUSTOMERS") that has about
500,000 rows on it. I need to do a "SELECT" to retrieve rows from a
particular company, ordered by company and zip code. I have several
indexes, including an index for the primary key ("COMPANY" and
"CUSTOMER_NUMBER"), and an index by company and zip code. The select
statement I am using is quite simple (values are just examples) --
SELECT (all fields) from CUSTOMERS where COMPANY=200 and ZIPCODE >
02138 order by COMPANY , ZIPCODE
This takes a long time to run, and when I analyze the execution plan,
I see that it spends most of its time doing a SORT and then a scan
over the primary key index. Even if I use the "WITH" to try and force
it to use the index, no change.
However, when I limit the rows being returned, like this --
SELECT TOP 1500 (all fields) from CUSTOMERS where COMPANY=200 and
ZIPCODE > 02138 order by COMPANY , ZIPCODE
It *does* use the index. This makes no sense to me -- I am assuming
that it has something to do with the number of rows being returned,
but in both cases, it is going to need to do the sort to deliver the
rows in sequence. Anybody have any idea why SQL 2005 would not be
using the index in the first case, and what is it about the "TOP"
keyword that is making it use the index?