Оператор create index создает индекс для конкретной таблицы. Общая форма этого оператора:

create  [unique]   [clustered  |nonclustered]  index index_name on table_name {columnl  [asc i desc]  ,   ...) [ include  ( column_name [,...])] [with

[fillfactor=n]

[[,   ]   pad_index = [on   i  off}]

[[,   ]   drop_existing =  [on   |   off}]

[[,   ]  sort_in_tempdb = [on  |  off}]

[[,   ]   ignore_dup_key = (on  |  off}]

[[,   ]  allow_row_locks = {on  i  off}]

[[,   ] allow_page_locks = [on  |  off}]

[[,   ]  statistics_norecompute = [on  i  off}]

[[, ] online = {on I off}]] [on file_group | «default»]

Здесь index_name задает имя создаваемого индекса. Индекс может быть создан для одного или более столбцов одной таблицы (tabie_name). coiumni - имя столбца, для которого создается индекс. Как вы можете видеть в форме оператора create index, вы можете создавать индекс для нескольких столбцов таблицы. Database Engine также поддерживает индексы и для представлений. Подобные представления, называемые индексированными представлениями, обсуждаются в следующей главе.

Индекс может быть простым или составным. Простой индекс имеет один столбец, в то время как составной индекс создан более чем для одного столбца. Каждый составной индекс имеет некоторые ограничения, связанные с его длиной и количеством столбцов. Максимальный размер индекса- 900 байтов, при этом индекс может содержать не более 16 столбцов.

Опция unique указывает, что каждое значение данных может появляться только один раз в индексированном столбце. Для уникального составного индекса комбинация значений данных во всех столбцах каждой строки должна быть уникальной. Если unique не задано, допустимы дубликаты в индексируемом столбце (столбцах).

Опция clustered задает кластеризованный индекс. Опция nonclustered (значение по умолчанию) указывает, что индекс не изменяет порядок строк в таблице. Database Engine допускает максимум 249 некластеризованных индексов для одной таблицы.

Database Engine имеет улучшенную поддержку индексов, которые упорядочены по убыванию значений столбца. Опция asc после имени столбца указывает, что индекс создается с возрастающим порядком значений столбца, тогда как desc задает убывающий порядок. Это дает вам больше гибкости в плане использования индексов. Убывающие индексы должны быть созданы, когда вы создаете составной индекс для столбцов, которые имеют противоположные направления сортировки.

Опция include позволяет вам задавать неключевые столбцы, которые добавляются в страницы листьев некластеризованного индекса. Имена столбцов не могут повторяться в списке include и не могут быть одновременно использованы в качестве ключевых и неключевых столбцов. Чтобы понять преимущества опции include, вам нужно знать, что такое покрывающий индекс. Существенное повышение производительности может быть достигнуто, когда все столбцы в запросе включены в индекс, потому что оптимизатор запросов может локализовать все значения столбцов в индексных страницах без необходимости доступа к данным таблицы. Эта возможность называется покрывающим индексом или покрывающим запросом. Следовательно, если вы включаете дополнительные неключевые столбцы в страницы листьев некла-стеризованного индекса, может быть покрыто большее количество запросов, а их производительность может быть значительно более высокой. (Дальнейшее обсуждение этой темы, равно как и пример того, как оптимизатор запросов обрабатывает покрывающие индексы, см. в разд. «Покрывающий индекс» далее В этом разделе).

Опция FILLFACTOR=n определяет процент заполнения каждой индексной страницы во время создания индекса. Вы можете задать значение fillfactor в диапазоне от 1 до 100. Если значение л установлено в 100, то каждая индексная страница будет заполнена на 100%, т. е. существующая страница листа индекса так же, как и страница, не относящаяся к листу, не будет иметь места для добавления новых строк. По этой причине такое значение рекомендуется использовать только для статических таблиц. Значение по умолчанию 0 также указывает на то, что страница листа индекса будет заполнена полностью и каждая промежуточная страница содержит место для одной записи.

Если вы установите значение опции fillfactor в значение между 1 и 99, то новая индексная структура будет создана со страницами листьев, которые не будут заполнены полностью. Чем больше значение fillfactor, тем меньше объем памяти, который остается свободным на индексной странице. Например, установка опции fillfactor в значение 60 означает, что 40% на каждой странице листа индекса остается свободным для дальнейшего добавления строк индекса. (Индексная строка будет добавлена, когда вы выполняете либо оператор insert, либо оператор update.) По этой причине значение 60 будет разумным решением для таблиц с довольно частым изменением данных. Для всех значений опции fillfactor между 1 и 99 все промежуточные страницы, не относящиеся к листьям, будут содержать свободное место для одной записи.

Опция pad_index тесно связана с опцией fillfactor. Опция fillfactor в первую очередь задает процент свободного места, которое остается на странице листьев индекса. С другой стороны, опция pad_index указывает, что установка fillfactor должна быть применена как к индексным страницам, так и к страницам данных, имеющим отношение к этому индексу.

Опция drop_existing позволяет увеличить производительность при пересоздании кластеризованного индекса для таблицы, которая также имеет некластеризованный индекс. Подробности «Пересоздание индекса» далее В этом разделе.

Опция sort_in_tempdb служит для помещения в системную базу данных tempdb промежуточных данных сортировки, используемой при создании индекса. Это может дать преимущество, если база данных tempdb располагается на дисковом носителе, отличном от того, где находятся сами данные. (Опция dropexisting обсуждается в разд. «Пересоздание индекса» далее В этом разделе.)

Опция ignoredupkey указывает системе, что она должна игнорировать попытки добавления дубликатов значений в индексный столбец (столбцы). Эта опция должна быть использована только для того, чтобы исключить завершение длинной транзакции в случае, когда оператор insert добавляет дубликаты данных в индексируемый столбец (столбцы). Если активирована эта опция, и оператор insert пытается добавить строки, которые нарушат уникальность индекса, то система базы данных вернет предупреждение вместо того, чтобы выдавать ошибку для всего оператора. Database Engine не добавит строки, которые пытаются создать дубликаты ключевых значений, он просто проигнорирует такие строки и добавит все оставшиеся. (Если такая опция не установлена, то все операторы будут отменены.)

Опция allowrowlocks задает, что система использует блокировку строк, когда активирована эта опция (установлена в on). Аналогично, опция allowpagelocks задает, что система использует блокировку страниц, когда эта опция установлена в on. (Описание блокировок страниц и строк.)

Опция statistics_norecompute указывает, что статистика по указанному индексу не должна вычисляться заново автоматически. Опция on создает указанный индекс либо для файловой группы по умолчанию («default»), либо для указанной файловой группы (filegroup).

Если вы активируете опцию online, то можете создавать, пересоздавать или удалять индекс в диалоговом режиме. Эта опция допускает параллельные модификации индексируемой таблицы или данных кластеризованного индекса, а также ассоциированных индексов в процессе выполнения изменений индекса. Например, в то время как пересоздается кластеризованный индекс, вы можете продолжать выполнение изменений данных в таблице и выполнять запросы к этим данным. (Это означает, что исключительные блокировки данных соответствующей таблицы не используются в процессе пересоздания индекса).

В примере 10.1 показано создание некластеризованного индекса.

 

В примере 10.2 показано создание уникального составного индекса.

 

 

Создание уникального индекса для столбца, который уже содержит дубликаты значений, невозможно. Создание уникального индекса возможно, если каждое существующее значение данных (в том числе значение null) появляется лишь один раз. Точно так же любая попытка добавить или изменить существующее значение данных в столбец, включенный в существующий уникальный индекс, будет отвергнуто системой в случае дублирования значения.