Re: covering indexes

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 10/17/04 

Date: Sat, 16 Oct 2004 19:40:44 -0700

Hi Sri

I'm a bit surprised that you read lots of articles that suggest creating
covering indexes, but you've never read one that told you what a covering
index is.

A covering index is one that includes ALL the columns from a table that are
used in a query. Normally, all that is important for an index to be used is
that the index keys are referenced in your WHERE clause, but with a
covering index, ALL the columns (from the SELECT list, the GROUP BY and any
other clause of your query) are part of the nonclustered index. Because the
covering index has everything needed to satisfy your query, SQL Server never
needs to access the actual data pages; it can stay in the index to retrieve
all the results.

So you need to examine your queries that aren't using any of your indexes,
and see if you can create one or more indexes that cover them. SQL Server
will not create these indexes automatically (unless you think of
automatically as following suggestions of the Index Tuning Wizard.) 

-- 
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sri" <Sri@discussions.microsoft.com> wrote in message 
news:9EC780A6-9458-4A9A-A85E-0E241A1308A9@microsoft.com...
> In lot of atricles regarding sql server performacne i read about "consider
> using covering indexes" .
>
> my question is How do u create a covering index, can we create it 
> explicitly
> or
> sql server creates it automatically?
>
>
> Thanks
> Sri
>
>
>

Relevant Pages

  • 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
    ... > In lot of atricles regarding sql server performacne i read about "consider ... > using covering indexes". ...
    (microsoft.public.sqlserver.server)
  • Re: how sql server executes queries.
    ... That is up to the optimizer to determine that when it generates the query ... you want to know more please get ahold of "Inside SQL Server 2000" by Kalen ... "Sri" wrote in message ... > Does it evaluate the joins first and then process the where clause ...
    (microsoft.public.sqlserver.programming)
  • 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)