Поделиться через

Создание индексов с включенными столбцами

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure

В этой статье описывается, как добавить включенные (или неключевые) столбцы для расширения функциональности некластеризованных индексов в SQL Server с помощью SQL Server Management Studio или Transact-SQL. Добавление неключевых столбцов позволяет создавать некластеризованные индексы, покрывающие больше запросов. Это обусловлено следующими преимуществами неключевых столбцов.

  • Они могут содержать типы данных, не разрешенные для ключевых столбцов индекса.
  • Они не учитываются ядро СУБД при вычислении количества ключевых столбцов индекса или размера ключа индекса.

Индекс с неключевыми столбцами может значительно повысить производительность запроса, когда все столбцы запроса включены в индекс как ключевые или неключевые. Повышение производительности достигается, так как оптимизатор запросов может найти все значения столбцов в индексе; Доступ к данным таблицы или кластеризованного индекса не приводит к меньшему объему операций ввода-вывода диска.

Примечание.

Если индекс содержит все столбцы, на которых в запросе имеются ссылки, это обычно называется покрытием запроса.

Рекомендации по проектированию

  • Переопределите некластеризованные индексы с большим размером ключа индекса, чтобы только столбцы, используемые для поиска и уточняющего запроса, были ключевыми. Все остальные столбцы, покрывающие запрос, сделайте неключевыми столбцами. Таким образом, у вас будут все столбцы, необходимые для покрытия запроса, но сам ключ индекса является небольшим и эффективным.

  • Включите некластеризованные столбцы в некластеризованный индекс, чтобы избежать превышения текущих ограничений размера индекса не более 32 ключевых столбцов и максимального размера ключа индекса размером 1700 байт (16 ключевых столбцов и 900 байт до SQL Server 2016 (13.x)). Ядро СУБД не учитывает неключевые столбцы при вычислении количества ключевых столбцов индекса или размера ключа индекса.

  • Порядок неключевых столбцов в определении индекса не влияет на производительность запросов, использующих индекс.

  • Избегайте очень широких некластеризованных индексов, где включенные столбцы не представляют достаточно узкое подмножество базовых столбцов таблицы. При добавлении широких индексов всегда проверяйте, компенсирует ли стоимость обновления одного сверхширокого индекса стоимость чтения непосредственно из таблицы.

ограничения

  • Неключевые столбцы можно задавать только для некластеризованных индексов.

  • Все типы данных, за исключением text, ntext, и image могут использоваться как неключевые столбцы.

  • Вычисляемые столбцы, являющиеся детерминированными, в том числе точными или неточными, могут быть неключевыми столбцами. Дополнительные сведения см. в разделе "Индексы" для вычисляемых столбцов.

  • Вычисляемые столбцы, полученные на основе типов данных image, ntextи text , могут быть неключевыми столбцами, если тип данных этого вычисляемого столбца допустим в качестве неключевого индексного столбца.

  • Некие столбцы нельзя удалить из таблицы, если индекс этой таблицы не удаляется первым.

  • Некие столбцы нельзя изменить, за исключением следующих действий.

    • изменение поведения столбца в отношении значения NULL с NOT NULL на NULL;

    • увеличение длины столбцов типов varchar, nvarcharи varbinary .

Безопасность

Разрешения

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.

Создание индекса с неключевыми столбцами с помощью SQL Server Management Studio

  1. В обозреватель объектов выберите знак плюса, чтобы развернуть базу данных, содержащую таблицу, в которой требуется создать индекс с неключевыми столбцами.

  2. Чтобы развернуть папку Таблицы, выберите значок "плюс".

  3. Выберите знак плюса, чтобы развернуть таблицу, в которой требуется создать индекс с неключевыми столбцами.

  4. Щелкните правой кнопкой мыши папку Индексы, выберите Создать индекс и Некластеризованный индекс...

  5. В диалоговом окне Создание индекса на странице Общие введите имя нового индекса в поле Имя индекса .

  6. На вкладке "Ключевые столбцы индекса" нажмите кнопку "Добавить...".

  7. В диалоговом окне "Выбор столбцов" из table_name установите флажок или флажки столбца таблицы или столбцов, которые будут добавлены в индекс.

  8. Нажмите ОК.

  9. На вкладке "Включенные столбцы " нажмите кнопку "Добавить...".

  10. В диалоговом окне Выбор столбцов изимя_таблицы установите флажки для столбцов таблицы, добавляемых в индекс в качестве неключевых столбцов.

  11. Нажмите ОК.

  12. В диалоговом окне "Создать индекс" нажмите кнопку "ОК".

Создание индекса с неключевыми столбцами с помощью Transact-SQL

  1. В обозревателе объектов подключитесь к экземпляру ядра СУБД.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте приведенный ниже пример в окно запроса и нажмите кнопку Выполнить.

    USE AdventureWorks2022;
GO
-- Creates a nonclustered index on the Person.Address table with four included (nonkey) columns.
-- index key column is PostalCode and the nonkey columns are
-- AddressLine1, AddressLine2, City, and StateProvinceID.
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO

Связанный контент