Создание и управление индексами

Создание индекса командами языка Transact – SQL производится следующим

образом:

· автоматически при создании первичного ключа, когда создается кластерный индекс (если не указан параметр NONCLUSTERED);

· автоматически при реализации ограничения целостности UNIQUE, когда создается не кластерный индекс;

· автоматически при создании таблицы, когда для столбца указываются параметры CLUSTERED или NONCLUSTERED;

· с помощью специальной команды CREATE INDEX.

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

Формат команды для явного создания индекса следующий:

CREATE [UNIQVE] [CLUSTERED\NONCLUSTERED] INDEX

Имя индекса

ON {Имя индекса\Имя представления}

(column[ASC\DESC] [,…n])

[WITH [PAD_INDEX]

[[,] FILLFACTOR = Фактор заполнения]

[[,] IGNOR_DUP_KEY]

[[,] DROP_EXISTING]

[[,] STATISTICS_NORECOMPUTE]

[[,] SORT_IN_TEMP_DB]

]

[ON Имя группы файлов]

Если автоматическое создание кластерного индекса не предполагается, то перед созданием не кластерного индекса надо создать кластерный, так как некластерный индекс всегда ссылается на кластерный. Можно создать 249 некластерных индексов с использованием до 16 столбцов в каждом индекс, при этом общая длина индекса не должна превышать 900 байтов. Столбцы с типами данных text, ntext или image в индексах не допускаются. Порядок столбцов при определении ключа очень важен. Желательно их указывать в порядке возрастания длины данных. Параметры ASC и DESC определяют метод сортировки ключевых элементов – соответственно по возрастанию или по убыванию.

Параметр PAD_INDEX обеспечивает резервирование на каждой странице индекса места для вставки новых записей и используется вместе с параметром FILLFACTOR.

Параметр IGNORE_DUP_KEY не приводит к отказу транзакции при добавлении дублирующих строк, при этом сами дублирующие строки игнорируются, и сервером выдается сообщение об ошибке. Остальные параметры команды используются редко. Созданный тем или иным способом индекс, можно переименовать с помощью системной хранимой процедуры sp_rename, можно его удалить командой DROP INDEX или перестроить для упорядочивания свободного места на индексных страницах, используя команды DROP INDEX и CREATE INDEX или команду DBCC DBREINDEX. Для получения информации об индексах используется системная хранимая процедура:

sp_helpindex [@objname] ‘name’, где name – имя рассматриваемой таблицы текущей базы данных.

Для просмотра индивидуальных свойств конкретного индекса следует применять команду:

INDEXPROPERTY (table_ID, index, property), в которой table_ID = OBJECT_ID (имя таблицы) – идентификационный номер таблицы, index – имя индекса, а property – рассматриваемое свойство: Index Depth (глубина индекса), Is Clustered (кластерный), Is Unique (уникальный) и др.

Для сбора и анализа статических данных при использовании индексов используются следующие команды и процедуры: CREATE STATISTICS, UPDATE STATISTICS, sp_autostats, sp_statistics и др.