What are key columns and INCLUDE columns in an index? How to use them correctly?

In database indexes, key columns are those columns that form the index itself and participate in searching and sorting. They determine the order and structure of the index.

INCLUDE columns are additional columns that do not participate in the index key but are included in the index as covering data. They allow executing queries without accessing the main table if all the required columns are in the index.

Proper usage:

• Include in key columns the fields that are frequently used in filters and sorts.
• Add to INCLUDE columns the fields needed for selection but do not affect the search, to avoid additional table accesses.

Example in SQL Server:

CREATE INDEX IX_Orders_CustomerDate
ON Orders (CustomerID, OrderDate) -- key columns
INCLUDE (TotalAmount, Status); -- columns for query coverage

This index will speed up queries filtering by CustomerID and OrderDate and selecting TotalAmount and Status without accessing the table.