Hi there, I have a table with 5 columns. ID,CustID ,FormID, DateAdded,DateUpdated. Now i have to query the table like below: Select Distinct CustID,FormID From tblTable where CustID = @CustID and ((DateAdded between @SDate and @EDate) or (DateUpdated between @SDate and @EDate)) So should i just create one non clustered index on all these 4 fields or should i create 2 separate indeces ,one on CustID ,FormID and second of DateAdded,DateUpdated ,CustID Please advice! Thanks!
Hi Danny, As per my understanding you have an ID column which may be an IDENTITY column if I am not wrong. If so then you can create a Primary Index on it which is a good practice. Then coming to your 2nd point: [quote user="danny123"]So should i just create one non clustered index on all these 4 fields or should i create 2 separate indeces ,one on CustID ,FormID and second of DateAdded,DateUpdated ,CustID[/quote] I think you will not get more performance benefit from creating one index on all 4 columns rather than you can create non-clustered index on CustID and DateAdded, DateUpdated separately as per the query in your post. Hope it will help you. Thanks, Sandy.
I doubt that both ideas will be very useful at all. Because of the way the query is expressed with the OR clause I don't think SQL Server will really consider the indices anyway. It might be worth trying out what happens when you rewrite it to a UNION like Select CustID,FormID From tblTable where CustID = @CustID and DateAdded between @SDate and @EDate UNION Select CustID,FormID From tblTable where CustID = @CustID and DateUpdated between @SDate and @EDate with 2 indices on {CustID, DateAdded, FormID} and {CustID, DateUpdated, FormID}
