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 |
How to modify an existing user defined function?
Explain external key management in sql server 2008
what is unique and xaml nonclustered index
What is BCP? When does it used in sql server 2012?
0 Answers TryTechnicals Pvt Ltd,
Every night you run a full backup after every 3 three hours you make a differential backup every hour you make an incremental backup in a worst-case scenario, how much work you can lose?
What are the disadvantages of using querystrings to send data from one page to another?
How to create an index on a view?
What are the four main query statements?
How to define output parameters in stored procedures?
What is the Disadvantage of indexed sequential file.
Explain the different index configurations a table can have?
When does the auto update index statistics feature in sql server turn itself on?q) what specific conditions database should meet, before you can bulk copy data into it using bcp?