Posted by Gert-Jan Strik on 06/28/05 22:45

paul wrote:
>
> Hi, i have a table like this
> CREATE TABLE dbo.test
> (
> num int NOT NULL,
> ename char(80),
> eadress char(200),
> archived char(1)
> PRIMARY KEY CLUSTERED (num)
> )
>
> create index i_archived on dbo.test(archived)
>
> the are 500000 rows in this table, and the archived field contain 15000 'Y'
> and 485000 'N'
>
> When i issue a select * from test where archived='Y',
> the path choosed is the index scan clustered and not the index i_archived
>
> the stats are updated every day.
>
> did i miss something ?
>
> thx

You probably did not miss anything. Does the tables have less than 15000
pages? In that case, SQL-Server probably thinks it is cheaper to scan
the table than it is to lookup 15000 rows based on the index. Which plan
is fastest will in practice depend on the amount of cached data. With a
cold (empty) cache, scanning the table will probably be faster. If you
know you have a hot cache for this query, then you could consider adding
an index hint.

HTH,
Gert-Jan

[Back to original message]