What is Covering Indexes?
Plz explain with example
Answers were Sorted based on User's Feedback
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 |
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 |
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 |
Explain user defined functions?
WHAT IS TRIGGERS IN SQL? AND WHAT IS THE BENIFIT?
What area unit the various kinds of info compression introduced in sql server 2008?
What are statistics?
Explain about analysis services?
What does REVERT do in SQL Server 2005?
What types of Joins are possible with Sql Server?
What Is Rdbms?
What are the differences between user defined functions and stored procedures?
Explain what is lock escalation and what is its purpose?
What are the encryption mechanisms in sql server?
Explain how to use linked server?