What is Covering Indexes? Plz explain with example

What is Covering Indexes?
Plz explain with example

Question Posted / senthilsjc

3 Answers
9638 Views
I also Faced
E-Mail Answers

Answers were Sorted based on User's Feedback

What is Covering Indexes? Plz explain with example..

An index covers the query if all the columns specified in
the query are part of the index. These are the columns that
are all columns referenced in the query, not just columns in
a WHERE clause.

eq:-SELECT DISTINCT lower(orig_airport) FROM Flights

here orig_airport is a index column.

Is This Answer Correct ?

3 Yes

1 No

What is Covering Indexes? Plz explain with example..

Answer / binyam

A covering index is a non clustered index built upon all the
columns required to satisfy a SQL query without going to the
base table. If a query encounters an index and does not need
to refer to the underlying data table at all, then the index
can be considered a covering index.
To understand a covering index it is very important first
understand non clustered index. a non clustered index does
not affect the order of the data in the table pages,because
the leaf pages of a non clustered index and the data pages
of the table are separate .A pointer is required to navigate
from an index row to the data row. so when a query requests
columns that are not part of the non clustered index chosen
by the optimizer , a lookup is required .The lookup fetches
the corresponding data row from the table by the following
row locator value from the index row, requiring a logical
read on the data page besides the logical read on the index
page. However, if all the columns required by the query are
available in the index itself , them access to the data page
not required then this known as a covering index.

Is This Answer Correct ?

2 Yes

0 No

What is Covering Indexes? Plz explain with example..

Answer / virgilio

A covering index is when you run a Query and you use an
indexed field in you where Clause:

*--Primary Key Creates aclustered Index
Create table Students
(
Studentid int Primary Key not Null, *
StudentName Nvarchar(20) Null,
StudentLastName Nvarchar(20) Null
)

Se;ect * from Students where StudentID = 20

Is This Answer Correct ?

1 Yes

2 No

Post New Answer