Baskar B.V

This can be a very dangerous practice. The concern with this is right index today may not be the right index tomorrow. If this begins to perform poorly in a year’s time, as the data set grows, you will be required to do a patch and deploy new code to fix a hardcoded index. The same thing goes for forcing the type of join (such as a hash join). I have seen a few (very few) cases where there was no other option, but in most cases there are ways to “help” sql pick the right index. The query optimizer will do a nice job, in most cases of picking the right index. In a case where it does not, it is your job to figure out why sql skips the index that you know will perform better. Many people believe that the less joins and the less filters in a where clause, the better. That is not always the case. Sometimes additional filters are necessary to help the index. Sometimes additional joins will help the index. For example, if I have a table that has a primary key on site_id, date, item_id. It contains columns with transaction_count and sales_amt. If I want to pull sales across multiple days for multiple sites, the optimizer is likely to try to scan the table. If I have a table with the day status in there per site, I will join the sales table to the day_status table on site_id and date and then apply my filters to the day_status table. This will give a direct, one-to-one match on the index and the query will work as expected. A great book that helps explain how to tune queries is SQL Server Query Performance Tuning Distilled -- http://www.amazon.com/Server-Performance-Tuning-Distilled-Second/dp/1590594215/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1214584001&sr=8-1

Cheers,
John

In this scenerio, we are sure the query will be based on date alone and that is reason why we specify forced index here.

But curious to know, why the index is not applied automatically when we use pass variable to the where clause that involves date field. Let me know if you have any clue.