sql server programming: Table -- Survey_invites
Primary key Clustered index on (survey_id,email_id).
Query 1
select * from survey_invites where survey_id='003' -- by default Index not
used ( need to give hint to make use of index)
with hint it takes 1 sec v/s 3 min without hint !!!
Query 2
select * from survey_invites where survey_id='003' and email_id='nnn' -- by
default Index used
But in SQL 2000 SP3 by default for both Query1 and Query2 the index was
used.
Is this a known problem in SQL 7.0 ? any help appreciated .?

Thanks Binu

The optimizer changes with each release. Generally speaking, fewer than
30-5% of the rows must be returned for a non-clustered index to be used...
Clustered indexes are almost always useful....Make sure index statistics are
up to date, and see what percentage of rows are returned by each query.

--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)

I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org

[quoted text, click to view]

Binu,

The fact SQL-Server 2000 doest a better job does not mean that
SQL-Server 7.0 has "a problem", or even worse "a known problem"!

You did not specify the data type of the survey_id column. Make sure you
use the same data type for the column definition and any literal you
compare it to. For your query, survey_id should be defined as char or
varchar.

If it is not (for example it is defined as int), then data type
conversion may prevent the usage of an index.

Especially in your case. The relevant index is clustered. If the data
type is correct, the clustered index will definitely be seeked!

Hope this helps,
Gert-Jan

[quoted text, click to view]