Hi Boris,

>Thank you Walter.
>I really don't want to mess with SQL Optimizer job.
>I just read that INDEX SEEK is always faster than TABLE SCAN and I just wanted to optimize my query to take all benefits of the index.

I don't know where you read that, but that is false IMO unless the keys are ordered forward (See the comments in the optimzation plan) and the table lookups is done later or directly read from the keys. Of course much depends on the nature of the query and the plan.

However, it is not always possible to have the keys ordered forward, esspecially when not all columns in the expressions filtering and joining the table on the right are in the same index. In that case the keys will not be ordered forward.

Now its important to know that an INDEX SEEK (e.g. in a LOOP JOIN) is done for each and every record on the left. So the INDEX SEEK is executed multiple times. It is sometimes faster, esspecially if all or nearly all rows from the table on the right have to be read anyways, to do a table scan on the right table and do a merge or hash join as then the index contention and I/O is bypassed. Esspecially when tables are small, this could make a difference.

In your case, I would not worry too much. The execution plan changes when tables grow and the selective nature of the query changes (Cost base). Of course it does not hurt to keep an eye on it, or test it with huge amounts of data in a real time environment.