SQL Server Performance Forum – Threads Archive

What is a covered index?

Recently I have heard of a new word covered index.I am wondering what is this all about.

A covered index is an index that can satisfy a query just by its index keys without having the need to touch the data pages. This is one of the fastest methods to retrieve data in SQL Server. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

How can I create a covered index in my table.Does covered index needs reindexing..like the one we do with nonclustured index using dbcc reindex.Does dbcc reindex will reindex covered index.

1. Just like you create other indexes.
2. Yes, it is just a normal index.
3. Yes. WBR, Vlad A. Scherbinin

Maybe a small sample will help understanding.
CREATE TABLE #t1
(
cID INT IDENTITY PRIMARY KEY
, c1 INT
)
GO
CREATE NONCLUSTERED INDEX ix_t1 ON t1(cID, c1) SELECT cID, c1 FROM #t1 When you fire the above SELECT statement, SQL Server is able to satisfy the SELECT just by using the NONCLUSTERED index ix_t1, because this index contains all informations that are requested. That is, the index covers the query. A covered index is no special index construct. The index itself is just an ordinary NONCLUSTERED index just like any other one. You have therefore to maintain it just like any other index. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

FMI
www.sql-server-performance.com/covering_indexes.asp

]]>