Hello,

See the queries below, the first one does the clustered index scan when the
2nd one does the index seek via nonclustered index idx_Date1.  Does anyone
know why?  If the table is large, it's less efficient to use clustered index
scan.  Is there a way to force it to use the index seek?  Another strange
part is, the actual execution path is decided based on the index
availability. The first table has 2 non-clustered vs. 1 for 2nd table. Thank
you for your comment and advice.

--- TABLE DEFINITIONS --------------

create table TEST1
(
 ClusteredId uniqueidentifier primary key,
    Date1 datetime,
 date2 datetime
)
go
create nonclustered index idx_Date1 on Test1( date1 )
create nonclustered index idx_Date2 on test1( date2 )

go
create table TEST2
(
 ClusteredId uniqueidentifier primary key,
    Date1 datetime
)
go
create nonclustered index idx_Date1 on Test2( date1 )

---- QUERIES ------------------------
select *
from test1
where date1 >= '1/1/06' and date1 <= '12/1/07'

go
select *
from test2
where date1 >= '1/1/06' and date1 <= '12/1/07'

It's because there's a covering index for your second query - the clustering
key is included in nonclustered indexes (PRIMARY KEY defaults to CLUSTERED)
so the 2nd query can be satisfied by looking at only the index. Since 'date1'
leads the index it can seek instead of scan.

The first query would require a bookmark lookup back to the clustered data
to satisify the query, and SQL Server determined that a scan would be more
efficient than a bookmark lookup given the data it contains (or doesn't
contain). This is why it's considered bad practice to use "SELECT *" in
production code.

You can force use of an index but it's not usually a good idea -- see TABLE
HINTS or WITH(INDEX) in BOL.

HTH   - KH