paul (no****@nomail.com) writes:
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 ?
The fine print that a non-clustered index is not always useful.
The row size is 286 bytes (columns + null bit mask). That makes for 28
28 rows per page when full. For 500000 rows that's 17854 pages to
scan. For the non-clustered index, there's 15000 bookmark lookups + the
index to scan, but that is not likely be more than some 100 pages.
Really why the optimizer goes for the clustered index, I don't know,
but such things like read-ahead reads being possible may be part of
it.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp