Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

select range in multiple non-clustered index'es


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

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc