Posted by smithabreddy on 06/09/06 11:22

I created a table called tblNames with nonClustered index defined on
lastname,firstname (composite index).

select * from tblnames where lastname = 'smith'--Performed an Index
Seek

select * from tblnames where lastname = 'smith' and firstname =
'john'--Performed an Index Seek

select * from tblnames where firstname = 'john'--Performed a Table Scan

select * from tblnames where firstname = 'john' and lastname =
'smith'--Performed an Index Seek

ZeldorBlat wrote:
> Alexander Kuznetsov wrote:
> > Baihao Yuan wrote:
> > > Hi,
> > >
> > > I created a composite index (lastname, firstname). I know the following
> > > queries will use this index:
> > >
> > > WHERE lastname = ...
> > > WHERE lastname = ... AND firstname = ...
> > >
> > > Also this won't use the index:
> > > WHERE firstname = ...
> > >
> >
> > Not necessarily. Consider the following query:
> >
> > select lastname, firstname from some_table where firstname = ...
> >
> > It will use the index, and, more to the point, it will not touch the
> > table at all - the index already has all the information the query
> > needs. It is called "index covering".
>
> No, it won't. If you had a list of people on a piece of paper, sorted
> by last name and then by first name, explain how you would use that
> list to find everyone with a first name of "Joe" without looking
> through the entire list.

• Next in forum: Re: Backup log - will it truncate?
• Prev in forum: Re: i want ot convert xml data format into the general data format
• Thread view: Re: composite index and column order

[Reply to this message]