Covering Index
Covering index is also a type of composite index which covers a query. In other words, it includes all the columns that are needed to execute a query.
If you know a particluar query is being used often in the application, then we should define a covering index on the table that will include all the columns referenced in the SELECT, JOIN and WHERE clause of the query. As a result, the SQL Server doesn’t have to look up the actual data in the table, thus reducing the I/O and boosting performance. One needs to be careful while defining a covering index , because if it gets too big, then its overhad can outweigh its benefits.
In the composite index, the order of the columns are very vital but incase of covering indexes, the order is not important because even If the wHERE Clause in a query does not specify the first column of an existing composite index, the query optimizer still use it because it contains all of the columns referenced in the query.
If you want to create a covering index, it is a good practise to modify the existing indexes that exists on the table. For example if you want to define a covering index for columns Id and Name and the index for column ID is already available then, instead of creating a new covering index , modify the index on ID to be a composite index on ID and Name.