SQL Server Performance

What is a covered index?

Discussion in 'General DBA Questions' started by aaronsandy, Jul 25, 2005.

  1. aaronsandy New Member

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

    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)
  3. aaronsandy New Member

    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.
  4. Ray D New Member

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

    WBR, Vlad A. Scherbinin
  5. FrankKalis Moderator

    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)
  6. dineshasanka Moderator

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

Share This Page