prakash.kumar3669 (1/17/2013)

---

In my table (large one) there is mainly three select queries:
1) select * from Product where productid=101
Here productid is primary key so it is clustered index, so no problem here.

2) select * from Product where IsDeleted=0 order by SubmitDate Desc
3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc

for 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.

How to set index for 2) and 3)

Do you always require "SELECT *" in your every query ?

prakash.kumar3669 (1/17/2013)

---

in other words covering queries in not an option i guess.

Why not?

prakash.kumar3669 (1/17/2013)

---

What i have read from online articles, its important to index the columns which comes in where condition.

prakash.kumar3669 (1/17/2013)

---

if i have 5 columns to retrieve in my queries then what's the best way of indexing ?

For better solution.post that Query

GilaMonster (1/17/2013)

---

Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)

btw, that's a rather odd predicate on the date column...