SQL | Covering index
A covering index is an index which is made up of all (or more) of the columns required to satisfy a query as key columns of the index.
When a covering index can be used to execute a query, fewer IO operations are required since the optimizer no longer has to perform extra lookups to retrieve the actual table data.
Create table Student
(
Roll_No int primary key,
Name varchar(50),
Gender varchar(30),
Mob_No bigint
);
CREATE NONCLUSTERED INDEX IX_Student_Name_Gender ON Student(Name ASC,Gender ASC);
The following TSQL query can now be executed by only accessing the new index we just created since all columns in the query are part of the index.
SELECT Name, Gender FROM Student Order By Gender;
