Re: Covering indexes versus column order in Delaney

From: DW (None)
Date: 11/05/04 

Date: Fri, 05 Nov 2004 09:25:12 -0800

"Kalen Delaney" <replies@public_newsgroups.com> wrote in
news:##OwB2fvEHA.3152@TK2MSFTNGP14.phx.gbl:

> Hi DW
>
> First, you got the quote a bit wrong. The word 'clustered' is not
> there. Covering indexes really only apply to nonclustered indexes.
>

You're right, of coure, the word "clustered" is not there. I don't know
where that came from. ;-)

> It's absolutely vital that you understand the difference between
> clustered and nonclustered indexes, and if you don't, a covering index
> will never really make sense.
>
> A nonclustered index has all the index key columns in the leaf level
> of the index, in order, so if the query is covered, you never have to
> go to the actual data pages. This is what makes a covering index fast.
>
>>>If I selected some other field that was not in the index, the query
>>>would no longer be "covered" by the index? <<
>
> Yes, that is correct.
>
>>>Pay attention to column order. ... For example, if an index has a
>>>composite key of last_name, first_name, that index ... is not useful
>>>for
> a query such as WHERE first_name='John'... um, doesn't that
> statement
> contradict page 827? <<
>
> The assumption here is that this index is NOT a covering index, and
> that there are other columns in the query that are not part of the
> index.
>

Yes, but that index will at least tell SQL which data pages it needs to
fetch, right? It doesn't need to scan the whole table, at least, and it's
better to have this noncovering index than not to have it, I hope! The
comment that this index is "not useful" is still a bit confusing. If it's
not useful, then would the performance be the same if the index were
dropped?

I understand the difference in a clustered versus nonclustered index, and
your comments here (and others' comments) have helped. The distinction is
apparently what other fields are in the query, which determines whether SQL
has to go read the data pages. It just SEEMED that the book contradicted
itself.

So the concept of a covering index is that the index pages contain all the
data required to satisfy all the fields in the query.

My situation is that I have a transaction table with a composite key
consisting of account number, transaction date, and transaction sequence
number, and I often need to look at all of last month's transactions (and
do further sub-selects within those). So my question really is, since I
have that composite key, and it includes an index on transaction date, will
it help for me to add an additional, separate index on the transaction
date?

I had done some testing on this but I don't remember the results -- I'll
have to do some more.

Thanks for the help.

David Walker

Relevant Pages

  • Re: Covering indexes versus column order in Delaney
    ... Covering indexes really only apply to nonclustered indexes. ... index, in order, so if the query is covered, you never have to go to the ...
    (microsoft.public.sqlserver.programming)
  • Re: Composite Index?
    ... would be returned by the concatenation, then doing the index seek (assuming ... one might suggest that this query could be rewritten to eliminate ... > A covering index scan would improve performance over a table scan if the ... >>> 'resolve the concatenation criteria'. ...
    (microsoft.public.sqlserver.programming)
  • Re: bookmark lookup
    ... The reason why a covering ... limitation of SQL Server but simply how it is. ... to place a clustered index on the table for ColA. ... The query looks like this: ...
    (microsoft.public.sqlserver.programming)
  • Re: covering indexes
    ... covering indexes, but you've never read one that told you what a covering ... other clause of your query) are part of the nonclustered index. ... covering index has everything needed to satisfy your query, SQL Server never ... "Sri" wrote in message ...
    (microsoft.public.sqlserver.server)