>I don't think you cannot draw any conclusions from this example. SQL-servers cost based optimizer is usually (I won't say all the time) pretty good at determining the right execution plan. Sometimes a table scan is just faster than multiple index seeks. In order to determine what is faster you can force a certain execution plan by using index hints WITH (INDEX = ...) and the join hints LOOP, HASH and MERGE, and just measure it in the SQL profiler. In the beginning I just thought like you that I could fully understand and predict the execution plan as I pretty much could in VFP. But SQL server is different and therefore it is difficult to always understand why it choosing one execution plan over another. Some factors (not an extensive list) involved are:
>
>- Memory
>- Number of CPU's
>- The existence of particular indexes
>- The selectivity of the index and the values to retrieve
>- Whether an index is unique (primary or not)
>- The nature of the query
>- Whether certain information can by retrieved by indexes only
>- The size of the tables and indexes
>- The usage of clustered indexes
>
>It is far more difficult to determine the execution plan than under VFP. That is just the nature of the beast.
>
>I've hit many examples where a table scan just was faster than an iterating index seek, especially if the same records are retrieved again and again. There are many other tricks like getting the most out of compound indexes and full index coverage to speed up your queries if necessary.

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.
THANK YOU again.