Index Scan vs Index Seek

I have a really strange problem.

I execute this query:

declare @.cid int
set @.cid = 2003227

select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @.cid

select * from sales s, product p where p.product_Id = s.product_Id and customer_id = @.cid or @.cid = 0

3 Million rows in sales, 120000 in product.

The first does and index seek, the second an index scan.
The execution plan reports that the scan takes 99.87% of the cost, and the seek takes 0.13%

This problem obviously gets worse the bigger the dataset / query /etc.

The reason I query this, is because it never used to take this long to do index scans. Is there something i can change, something i can fix?

Any help would be appreciated.

Joshi believe that the 'OR' statement is causing a table scan(the leaf level of the clustered index)

consider this
i am going to assume that only one of these columns are indexed.
try creating an index on the other column, because if you query a table and use the 'OR' clause in your query and any of the columns are not indexed... SQL server will perform a table scan.|||Okay, If I simplify the queries to this:

declare @.c_ID int
set @.c_Id = 2003227

select * from sales where customer_id = @.C_ID
select * from sales where customer_id = @.C_ID or @.c_Id = 0

(There is a non-unique, non clustered index on the customer_Id column.)

The problem still happens. My question is - why does it use an index scan for the second query, and why does it take so damn long?

Josh

Index - which 1 to choose ?

good day, everyone

if i have a transaction table with fields below :

transaction_no, product_id, product_desc, product_qty, product_txn, transaction_date

can some expert here point out to me , which is the best cluster-index and non-clsuter index ?

and possible kindly please explain why is it so? i'm not good in database so just explain like to beginner

thank you very much for guidanceRead this:

http://www.sql-server-performance.com/mr_indexing.asp

Then come back if you have more questions.|||Hi nakata

Welcome to the forum :D

What are you going to do with the table? Index choice depends on this. Given its name I guess a lot of inserts. Many updates? Much querying? Any foreign keys? Any other fields you are likely to link with in any queries?

Are any of the fields likely to always increase in value for each successive record added? (e.g. the transaction_no and transaction_date look like good candidates)|||Damn these slow, clumsy fingers of mine :o And I forgot the phrase "monotonically increasing" :o :o