A Covering Index is a non-clustered index that contains ALL the columns required by a specific query. When a query is "Covered," SQL Server doesn't have to perform a Key Look-up. It gets everything it needs directly from the index, resulting in massive speed improvements.
You don't want to add 10 columns to your index key because that makes the index huge and slow to maintain. Instead, you use the INCLUDE clause. Included columns are only stored at the "Leaf Level" of the index. They aren't used for sorting, but they are there for the query to "grab" without a look-up.
CREATE NONCLUSTERED INDEX IX_Users_Email
ON Users(Email)
INCLUDE (FullName, DateOfBirth)Q: "When does a 'Covering Index' become a bad idea?"
Architect Answer: "When you overdo it. If you have an index that includes 20 columns, every single `INSERT` or `UPDATE` on that table must now also update that massive index. It slows down your writing performance significantly. You should only 'Cover' your most frequent, mission-critical queries, not every single query in the app."