covering indexes

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
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
>
>
|||"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?
>
Further to Kalens excellent reply, it does not have to be just one
non-clustered index. SQL can combine multiple non-clustered indexes to
cover the query.
Also the key to row ratio affects performance. If there are 2 columns
referenced in the query but 50 in the underlying table it will be faster
covering it than if there are 40 columns referenced in the query. Try to
avoid SELECT *.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.778 / Virus Database: 525 - Release Date: 15/10/2004