В чем разница между составным индексом и индексом с включенными столбцами

Что проверяют

Понимаете ли вы, как устроен некластерный индекс в SQL Server и чем ключ индекса отличается от «пассажиров» на листовом уровне. Следом обычно дают запрос и спрашивают, какой индекс под него построить и почему в плане появляется Key Lookup.

Как отвечать

Оба варианта это B-tree, разница в том, где лежат колонки:

• составной индекс: несколько колонок в ключе, CREATE INDEX IX_Orders ON Orders (CustomerId, OrderDate). Ключ хранится на всех уровнях дерева и отсортирован, поэтому индекс поддерживает поиск (Index Seek), диапазоны и ORDER BY. Работает слева направо: запрос только по OrderDate этим индексом эффективно не воспользуется;
• индекс с включёнными столбцами: ... (CustomerId) INCLUDE (Total, Status). Колонки из INCLUDE лежат только на листьях, в поиске и сортировке не участвуют, но позволяют отдать результат прямо из индекса, без похода в кластерный индекс;
• покрывающий индекс: так называют индекс, в котором есть всё, что нужно запросу. Типичная схема: колонки из WHERE и ORDER BY в ключ, колонки из SELECT в INCLUDE;
• ограничения ключа: не больше 16 колонок и 1700 байт для некластерного индекса, типы вроде nvarchar(max) в ключ не попадают. В INCLUDE их положить можно;
• уникальность: в UNIQUE-индексе уникальным считается только ключ, включённые колонки на неё не влияют.

Из C# это обычно задаётся через EF Core: HasIndex(o => new { o.CustomerId, o.OrderDate }) для составного ключа и .IncludeProperties(o => o.Total) для включённых колонок, провайдер SQL Server превратит это в INCLUDE в миграции. Проверять результат стоит по фактическому плану выполнения: исчез ли Key Lookup.

Где спотыкаются

Считают, что колонку из INCLUDE можно использовать для поиска. Фильтр по ней выполнится, но как проверка на уже прочитанных строках, а не как Seek.

Путают порядок колонок в составном ключе. Сначала идут колонки с условием равенства, потом колонка диапазона; наоборот индекс работает гораздо хуже.

Складывают в INCLUDE половину таблицы. Индекс раздувается, а каждое изменение этих колонок обновляет его копию, запись дорожает.