Создание и управление индексами. Создание и управление встроенными процедурами. Оптимизация производительности запросов. Управление транзакциями и блокировками, страница 2

Теперь вы знакомы с различными архитектурами индексов, и мы будем говорить про создание и удаление индексов, и получать информацию о существующих индексах

Создание и удаление индекса

Вы можете создать индекс с помощью оператора CREATE INDEX, а удалить можно с помощью оператора DROP INDEX. Вы должны быть владельцем базы данных, чтобы выполнить этот оператор.

Используйте оператор CREATE INDEX для создания индекса. Когда вы создаёте индекс на одну или более колонок в таблице, рассматривайте следующие факты и рекомендации:

  Сервер SQL автоматически создаёт индекс, когда создаётся ограничение PRIMARY KEY или UNIQUE.

  Вы должны быть владельцем базы данных, чтобы создавать индекс.

  Сервер SQL хранит информацию об индексах в системной таблице sysindexes.

  Прежде чем вы создадите индекс на колонку, определите, существуют ли индексы на эту колонку.

  Делайте индексы маленькими с помощью объявления их на колонки маленького размера. Обычно маленькие индексы более эффективны.

  Когда вы создаёте кластерный индекс, все существующие не кластерные индексы перестраиваются.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name

ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )

[ WITH < index_option > [ ,...n] ]

[ ON filegroup ]

< index_option > :: =

{ PAD_INDEX |

FILLFACTOR = fillfactor |

IGNORE_DUP_KEY |

DROP_EXISTING |

STATISTICS_NORECOMPUTE |

SORT_IN_TEMPDB 

}

Следующий пример создаёт кластерный индекс на колонку LastName:

CREATE CLUSTERED INDEX CL_lastname

ON employees(lastname)

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

  Сервер SQL освобождает дисковое пространство, занятое индексом в таблице;

  Вы не можете использовать оператор DROP INDEX с индексом, созданным на ограничения PRIMARY KEY или UNIQUE. Вы должны удалить ограничение, прежде чем удалять индекс.

  Когда вы удаляете таблицу, все индексы в таблице удаляются.

  Когда вы удаляете кластерный индекс, все не кластерные индексы автоматически перестраиваются.

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

  Удаление индекса невозможно в системных таблицах.

DROP INDEX 'table.index | view.index' [ ,...n ]

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

USE Northwind

DROP INDEX employees.CL_lastname

Создание уникального индекса

Уникальный индекс гарантирует, что все данные в колонке с таким индексом – уникальны, и не содержат повторяющихся значений. Когда вы создаёте уникальный индекс, рассматривайте следующие факты и рекомендации:

  Сервер SQL автоматически создаёт индекс, когда создаётся ограничение PRIMARY KEY или UNIQUE.

  Если таблица содержит данные, то SQL Server автоматически проверяет дублирующие значения, когда вы создаёте индекс.

  Сервер SQL проверяет дубликаты каждый раз, когда вы выполняете операторы INSERT или UPDATE. Если дубликат существует, то сервер отклоняет ваши операторы и возвращает сообщение об ошибке.

Если повторяющиеся значения существуют, когда вы создаёте уникальный индекс, операция CREATE INDEX отклоняется. Сервер возвращает сообщение об ошибке с первым дубликатом, но могут существовать и ещё дубликаты. Используйте следующий простой скрипт для любых таблиц для поиска дублирующих значений в колонке. Изменяйте текст, написанный наклонным шрифтом, специфичными для вас данными:

SELECT индексная колонка, COUNT(индексная колонка)

FROM имя таблицы

GROUP BY индексная колонка

HAVING COUNT (индексная колонка)>1

ORDER BY индексная колонка

Создание составного индекса

Составные индексы используют более одной колонки в качестве ключевого значения. Вы создаёте составные индексы:

  Когда две или более колонки чаще всего используются для поиска в качестве ключа;

  Если запрос ссылается только на колонки в индексе.

Для примера, телефонный справочник является хорошим примером. Справочник организован по фамилии. Вместе с фамилией для поиска регулярно используется имя, потому что часто существует много записей для одной фамилии с разными именами.