What is a covering index (INCLUDE in indexes)?

A covering index is an index that contains all the columns necessary to fulfill a specific query, allowing the DBMS to retrieve all data from the index without accessing the main table. In SQL Server and some other DBMSs, this is achieved using the INCLUDE keyword, which allows additional columns to be added to the index that are not part of the index key but are stored in the index structure.

For example, if you have an index on the UserId column, and your query selects UserId and UserName, you can create an index with UserId as the key and add UserName via INCLUDE. This speeds up the query because it eliminates the need for additional reads from the table.

Example of creating an index with INCLUDE in T-SQL:

CREATE INDEX IX_Users_UserId ON Users(UserId) INCLUDE (UserName);

Thus, a covering index reduces the number of data accesses and improves query performance, especially for queries with filtering and projection on a small set of columns.