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

CREATE INDEX (Transact-SQL)

  • Статья

Изменения: 14 апреля 2006 г.

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

Значок ссылки на разделСинтаксические обозначения в Transact-SQL

Синтаксис

Create Relational Index 
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_or_view_name
}

<relational_index_option> ::=
{
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Create XML Index 
CREATE [ PRIMARY ] XML INDEX index_name 
    ON <object> ( xml_column_name )
    [ USING XML INDEX xml_index_name 
        [ FOR { VALUE | PATH | PROPERTY } ] ]
    [ WITH ( <xml_index_option> [ ,...n ] ) ]
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_name. ] 
        table_name
}

<xml_index_option> ::=
{ 
    PAD_INDEX  = { ON | OFF }
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
}

Backward Compatible Relational Index
Important   The backward compatible relational index syntax structure will be removed in a future version of SQL Server. Avoid using this syntax structure in new development work, and plan to modify applications that currently use the feature. Use the syntax structure specified in <relational_index_option> instead.

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column_name [ ASC | DESC ] [ ,...n ] ) 
    [ WITH <backward_compatible_index_option> [ ,...n ] ]
    [ ON { filegroup_name | "default" } ]

<object> ::=
{
    [ database_name. [ owner_name ] . | owner_name. ] 
        table_or_view_name
}

<backward_compatible_index_option> ::=
{ 
    PAD_INDEX
  | FILLFACTOR = fillfactor
  | SORT_IN_TEMPDB
  | IGNORE_DUP_KEY
  | STATISTICS_NORECOMPUTE 
  | DROP_EXISTING 
}

Аргументы

  • UNIQUE
    Создает уникальный индекс для таблицы или представления. Уникальным является индекс, в котором не может быть двух строк с одним и тем же значением ключа индекса. Кластеризованный индекс представления должен быть уникальным.

    Компонент SQL Server 2005 Database Engine не позволяет создать уникальный индекс по столбцам, уже содержащим повторяющиеся значения, даже если параметру IGNORE_DUP_KEY присвоено значение ON. При попытке создания такого индекса компонент Database Engine выдает сообщение об ошибке. Прежде чем создавать уникальный индекс по такому столбцу или столбцам, необходимо удалить все повторяющиеся значения. Столбцы, используемые в уникальном индексе, должны иметь свойство NOT NULL, т. к. при создании индекса значения NULL рассматриваются как повторяющиеся.

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

    Представление с уникальным кластеризованным индексом называется индексированным. Создание уникального кластеризованного индекса физически материализует представление. Уникальный кластеризованный индекс для представлении должен быть создан до того, как для этого же представления будут определены какие-либо другие индексы. Дополнительные сведения см. в разделе Конструирование индексированных представлений.

    Создавайте кластеризованные индексы до создания любых некластеризованных. При создании кластеризованного индекса все существующие некластеризованные индексы таблицы перестраиваются.

    Если аргумент CLUSTERED не указан, создается некластеризованный индекс.

    ms188783.note(ru-ru,SQL.90).gifПримечание.
    Поскольку конечный уровень кластеризованного индекса и страницы данных — это по определению одно и то же, создание кластеризованного индекса и использование предложения ON partition_scheme_name или ON filegroup_name приводят к перемещению таблицы из файловой группы, в которой она была создана, в новую схему секционирования или файловую группу. Прежде чем создавать таблицы или индексы в определенных файловых группах, проверьте, какие группы доступны, и убедитесь в том, что в этих группах достаточно свободного места для индекса. Дополнительные сведения см. в разделе Определение требований к месту на диске для индексов.

  • NONCLUSTERED
    Создается индекс, который задает логический порядок таблицы. Логический порядок строк данных в некластеризованном индексе не влияет на их физический порядок. Дополнительные сведения см. в разделе Структуры некластеризованных индексов.

    Для каждой таблицы можно создать до 249 некластеризованных индексов, независимо от того, каким образом они создаются: неявно с помощью ограничений PRIMARY KEY и UNIQUE или явно с помощью инструкции CREATE INDEX.

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

    По умолчанию, используется значение NONCLUSTERED.

  • index_name
    Имя индекса. Имена индексов должны быть уникальными в пределах таблицы или представления, но не базы данных. Имена индексов должны соответствовать правилам для идентификаторов.

    Имена первичных XML-индексов не должны начинаться со следующих символов: #, ##, @ или @@.

  • column
    Столбец или столбцы, на которых основан индекс. Задайте несколько имен столбцов для создания составного индекса по объединенным значениям указанных столбцов. Столбцы, которые должны быть включены в составной индекс, указываются в скобках за аргументом table_or_view_name в порядке сортировки.

    В один составной ключ индекса могут входить до 16 столбцов. Все столбцы составного ключа индекса должны находиться в одной таблице или одном и том же представлении. Максимальный общий размер значений составного индекса равен 900 байт. Дополнительные сведения о столбцах переменной длины в составных индексах см. в разделе «Примечания».

    Столбцы с типами данных для больших объектов ntext, text, varchar(max), nvarchar(max), varbinary(max), xml или image не могут быть ключевыми столбцами для индекса. Кроме того, определение представления не может включать столбцы типов ntext, text и image, даже если они указаны в инструкции CREATE INDEX.

    Можно создавать индексы на столбцах с пользовательским типом данных CLR, если этот тип поддерживает двоичное упорядочение. Можно также создавать индексы на вычисляемых столбцах, определенных как вызовы методов для столбцов с пользовательскими типами данных, если эти методы помечены как детерминированные и не выполняют операции доступа к данным. Дополнительные сведения об индексировании столбцов с пользовательскими типами данных CLR см. в разделе Типы данных CLR, определяемые пользователем.

  • [ ASC | DESC ]
    Определяет сортировку значений заданного столбца индекса: по возрастанию или по убыванию. По умолчанию, используется аргумент ASC.

  • INCLUDE **(**column [ ,... n ] )
    Указывает неключевые столбцы, добавляемые на конечный уровень некластеризованного индекса. Некластеризованный индекс может быть уникальным или неуникальным.

    Максимальное количество включенных неключевых столбцов — 1023, минимальное — 1.

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

    Допускаются данные всех типов, кроме text, ntext и image. Индекс должен создаваться или перестраиваться в автономном режиме (ONLINE = OFF), если любой из заданных неключевых столбцов имеет тип данных varchar(max), nvarchar(max) или varbinary(max).

    Включенными столбцами могут быть детерминированные вычисляемые столбцы — как точные, так и неточные. Вычисляемые столбцы, производные от типов данных image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml, могут включаться как неключевые, если типы данных вычисляемых столбцов приемлем для включения. Дополнительные сведения см. в разделе Создание индексов вычисляемых столбцов.

  • ON partition_scheme_name**(column_name)**
    Задает схему секционирования, которая определяет файловые группы соответствующие секциям секционированного индекса. Схема секционирования должна быть уже создана в базе данных с помощью инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. Аргумент column_name задает столбец, по которому будет секционирован индекс. Этот столбец должен соответствовать типу данных, длине и точности аргумента функции секционирования, которую использует схема partition_scheme_name. Аргумент column_name может указывать на столбцы, не входящие в определение индекса. Можно указать любой столбец базовой таблицы, за исключением случая секционирования индекса UNIQUE, когда столбец column_name должен быть выбран из используемых в уникальном ключе. Это ограничение дает возможность компоненту Database Engine проверять уникальность значений ключа только в одной секции.

    ms188783.note(ru-ru,SQL.90).gifПримечание.
    При секционировании неуникального кластеризованного индекса компонент Database Engine по умолчанию добавляет столбец секционирования в список ключей кластеризованного индекса, если этого столбца еще нет в списке. При секционировании неуникального некластеризованного индекса компонент Database Engine добавляет столбец секционирования как неключевой (включенный) столбец индекса, если этого столбца еще нет в списке.

    Если аргумент partition_scheme_name или filegroup не задан и таблица секционирована, индекс помещается в ту же схему секционирования и с тем же столбцом секционирования, что и для базовой таблицы.

    Для XML-индекса задать схему секционирования невозможно. Если базовая таблица секционирована, XML-индекс использует ту же схему секционирования, что и таблица.

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

  • ON filegroup_name
    Создает заданный индекс в указанной файловой группе. Если местоположение не указано и таблица или представление не секционированы, индекс использует ту же файловую группу, что и базовые таблица или представление. Файловая группа уже должна существовать. XML-индексы используют ту же файловую группу, что и таблица.

  • ON "default"
    Создает заданный индекс в файловой группе, используемой по умолчанию.

    Слово «default» в этом контексте не является ключевым. Это идентификатор файловой группы, используемой по умолчанию, который должен быть ограничен, как например в выражении ON "default" или ON [default]. Если задано значение «default», параметр QUOTED_IDENTIFIER в текущем сеансе должен иметь значение ON. Это установка по умолчанию. Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL).

  • [PRIMARY] XML
    Создает XML-индекс по заданному столбцу типа xml. Если присутствует ключевое слово PRIMARY, создается кластеризованный индекс с ключом, образованным из ключа кластеризации таблицы пользователя и идентификатора XML-узла. Для каждой таблицы можно создать до 249 XML-индексов. При создании XML-индекса помните следующее.

    • Кластеризованный индекс должен существовать для первичного ключа таблицы пользователя.
    • Максимальное количество столбцов в ключе кластеризации таблицы пользователя — 15.
    • У каждого столбца типа xml в таблице может быть один первичный XML-индекс и несколько вторичных.
    • Чтобы создать вторичный XML-индекс для столбца типа xml, первичный XML-индекс для этого столбца уже должен существовать.
    • XML-индекс может быть создан только для одного столбца типа xml. Невозможно создать XML-индекс для столбца, не относящегося к типу xml, а также реляционный индекс для столбца типа xml.
    • Невозможно создать первичный или вторичный XML-индекс для столбца типа xml в представлении для переменной со столбцами типа xml, возвращающей табличное значение, или для переменных типа xml.
    • Невозможно создать первичный XML-индекс для вычисляемого столбца типа xml.
    • Значения параметров SET должны быть теми же, что и для индексированных представлений и индексов вычисляемых столбцов. В частности, параметр ARITHABORT должен быть в значении ON, если создается XML-индекс и если выполняется вставка, удаление или обновление значений в столбце типа xml. Дополнительные сведения см. в разделе Параметры SET, влияющие на результаты.

    Дополнительные сведения см. в разделе Индексы для столбцов типа данных xml.

  • xml_column_name
    Столбец типа xml, на котором основан индекс. В одном определении XML-индекса может быть задан только один столбец типа xml, но для одного столбца типа xml можно создать несколько вторичных XML-индексов.
  • USING XML INDEX xml_index_name
    Указывает первичный XML-индекс, который должен использоваться при создании вторичного XML-индекса.

  • FOR { VALUE | PATH | PROPERTY }
    Указывает тип вторичного XML-индекса.

    • VALUE
      Создает вторичный XML-индекс для столбцов, где ключевые столбцы (значение узла и путь) входят в первичный XML-индекс.
    • PATH
      Создает вторичный XML-индекс для столбцов, построенных на основе значений путей и узлов в первичном XML-индексе. Во вторичном индексе типа PATH значениями путей и узлов являются ключевые столбцы, обеспечивающие эффективный поиск путей.
    • PROPERTY
      Создает вторичный XML-индекс для столбцов (PK, пути и узла) первичного XML-индекса, где PK — первичный ключ базовой таблицы.
<object>::=

Полное или неполное имя индексируемого объекта.

  • database_name
    Имя базы данных.
  • schema_name
    Имя схемы, которой принадлежит таблица или представление.

  • table_or_view_name
    Имя индексируемой таблицы или представления.

    Чтобы создать индекса для представления, это представление оно должно быть определено с параметром SCHEMABINDING. Прежде чем создавать любой некластеризованный индекс для представления, необходимо создать уникальный кластеризованный индекс. Дополнительные сведения об индексированных представлениях см. в разделе «Примечания».

<relational_index_option>::=

Указывает параметры, которые должны использоваться при создании индекса.

  • PAD_INDEX = { ON | OFF }
    Задает разреженность индекса. Значение по умолчанию — OFF.

    • ON
      Процент свободного места, определяемый аргументом fillfactor, применяется к страницам индекса промежуточного уровня.
    • Значения OFF и fillfactor не заданы
      Страницы промежуточного уровня заполняются почти полностью, при этом остается достаточно места по крайней мере для одной строки максимального размера, возможного в этом индексе при заданном наборе ключей на промежуточных страницах.

    Аргумент PAD_INDEX имеет смысл только в случае, если присутствует аргумент FILLFACTOR, т. к. PAD_INDEX использует процент, указанный в FILLFACTOR. Если процент, заданный аргументом FILLFACTOR, недостаточно велик для размещения одной строки, компонент Database Engine внутренне переопределит это значение, чтобы обеспечить минимум. Количество строк на странице промежуточного уровня никогда не бывает менее двух даже при самых малых значениях аргумента fillfactor.

    Для обратной совместимости синтаксиса аргумент WITH PAD_INDEX эквивалентен аргументу WITH PAD_INDEX = ON.

  • FILLFACTOR **=**fillfactor
    Указывает, на сколько процентов должен компонент Database Engine заполнить страницы конечного уровня при создании или перестройке индекса. fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию — 0. Если fillfactor равен 100 или 0, компонент Database Engine создает индексы с полностью заполненными страницами конечного уровня.

    ms188783.note(ru-ru,SQL.90).gifПримечание.
    Значения коэффициента заполнения 0 и 100 равнозначны.

    Аргумент FILLFACTOR действует только при создании или перестройке индекса. Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Значение коэффициента заполнения можно увидеть в представлении каталога sys.indexes.

    ms188783.note(ru-ru,SQL.90).gifВажно!
    Создание кластеризованного индекса с аргументом FILLFACTOR меньше 100 влияет на объем пространства хранения, занимаемого данными, т. к. компонент Database Engine перераспределяет данные, когда создает кластеризованный индекс.

    Дополнительные сведения см. в разделе Коэффициент заполнения.

  • SORT_IN_TEMPDB = { ON | OFF }
    Указывает, сохранять ли временные результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.

    • ON
      Промежуточные результаты сортировки, используемые для создания индекса, хранятся в базе данных tempdb. Это может уменьшить время, необходимое для создания индекса, если база данных tempdb и база данных пользователя находятся на разных наборах дисков. Однако увеличивается объем пространства на диске, которое используется при создании индекса.
    • OFF
      Промежуточные результаты сортировки хранятся в той же базе данных, что и индекс.

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

    Для обратной совместимости синтаксиса аргумент WITH SORT_IN_TEMPDB эквивалентен аргументу WITH SORT_IN_TEMPDB = ON.

  • IGNORE_DUP_KEY = { ON | OFF }
    Определяет реакцию на ошибку, возникшую из-за дублирования значений ключа при вставке нескольких строк в уникальный кластеризованный или уникальный некластеризованный индекс. Значение по умолчанию — OFF.

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

    Аргумент IGNORE_DUP_KEY применяется только к операциям вставки, выполняемым после создания или перестройки индекса. Во время операций с индексами этот параметр не используется.

    Аргумент IGNORE_DUP_KEY не может принимать значение ON для XML-индексов и индексов для представлений.

    Для обратной совместимости синтаксиса аргумент WITH IGNORE_DUP_KEY эквивалентен аргументу WITH IGNORE_DUP_KEY = ON.

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Указывает, пересчитывается ли статистика распределения. Значение по умолчанию — OFF.

    • ON
      Устаревшая статистики автоматически не пересчитывается.
    • OFF
      Включено автоматическое обновление статистики.

    Для возобновления автоматического пересчета статистики присвойте аргументу STATISTICS_NORECOMPUTE значение OFF или выполните инструкцию UPDATE STATISTICS без предложения NORECOMPUTE.

    ms188783.note(ru-ru,SQL.90).gifВажно!
    Отключение автоматического обновления статистики распределения может помешать оптимизатору запросов выбирать оптимальные планы выполнения для запросов, включающих таблицы.

    Для обратной совместимости синтаксиса аргумент WITH STATISTICS_NORECOMPUTE эквивалентен аргументу WITH STATISTICS_NORECOMPUTE = ON.

  • DROP_EXISTING = { ON | OFF }
    Указывает, что названный существующий кластеризованный, некластеризованный или XML-индекс удаляется и перестраивается. Значение по умолчанию — OFF.

    • ON
      Существующий индекс удаляется и перестраивается. Указанное имя индекса должно совпадать с уже существующим индексом, но определение индекса может быть изменено. Например, можно указать другие столбцы, порядок сортировки, схему секционирования или параметры индекса.
    • OFF
      Выдается ошибка, если индекс с указанным именем уже существует.

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

    Для обратной совместимости синтаксиса аргумент WITH DROP_EXISTING эквивалентен аргументу WITH DROP_EXISTING = ON.

  • ONLINE = { ON | OFF }
    Определяет, будут ли базовые таблицы и соответствующие индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

    ms188783.note(ru-ru,SQL.90).gifПримечание.
    Фоновые операции с индексами доступны только в выпуске SQL Server 2005 Enterprise Edition.
    • ON
      Во время операций с индексами долгосрочные блокировки таблиц не поддерживаются. Во время основной фазы операций над индексом для таблицы источника удерживается только блокировка с намерением совмещаемого доступа (Intent Share, IS). Это дает возможность посылать запросы к базовой таблице и соответствующим индексам, а также их обновлять их. В начале операции в течение очень короткого времени на объекте источника удерживается совмещаемая блокировка (S). В конце операции источнику на короткое время требуется совмещаемая блокировка (S), если создается некластеризованный индекс, или блокировка изменения схемы (SCH-M), если создается или удаляется кластеризованный индекс в оперативном режиме, а также в случае перестройки кластеризованного или некластеризованного индекса. Если создается индекс для временной локальной таблицы, параметр ONLINE не может быть равен ON.
    • OFF
      Блокировки таблиц устанавливаются на время операции с индексом. Автономная операция с индексом, в ходе которой создается, перестраивается или удаляется кластеризованный или некластеризованный индекс, получает блокировку изменения схемы (SCH-M) для таблицы. Это предотвращает доступ всех остальных пользователей к базовой таблице во время операции. Автономная операция с индексами, создающая некластеризованный индекс, получает совмещаемую блокировку (S) для таблицы. Это лишает возможности обновить базовую таблицу, но позволяет выполнять операции чтения, например инструкции SELECT.

    Дополнительные сведения см. в разделе Об оперативных действиях над индексом. Дополнительные сведения о блокировках см. в разделе Режимы блокировки.

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

    • XML-индекс.
    • Индекс локальной временной таблицы.
    • Исходные уникальные кластеризованные индексы представлений.
    • Отключенные кластеризованные индексы.
    • кластеризованные индексы, если базовая таблица содержит типы данных LOB: image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml;
    • некластеризованные индексы, заданные со столбцами типа LOB;
      ms188783.note(ru-ru,SQL.90).gifПримечание.
      Неуникальный некластеризованный индекс может быть создан в фоновом режиме, если таблица содержит типы данных LOB, но ни один из этих столбцов не используется в определении индекса как ключевой или неключевой (включенный) столбец.

    Дополнительные сведения см. в разделе Оперативное выполнение операций над индексом.

  • ALLOW_ROW_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

    • ON
      Блокировки строк допустимы при обращении к индексу. Компонент Database Engine определяет, когда используются блокировки строк.
    • OFF
      Блокировки строк не используются.

  • ALLOW_PAGE_LOCKS = { ON | OFF }
    Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

    • ON
      Блокировки страниц допустимы при обращении к индексу. Компонент Database Engine определяет, когда используются блокировки страниц.
    • OFF
      Блокировки страниц не используются.

  • MAXDOP = max_degree_of_parallelism
    Переопределяет параметр конфигурации max degree of parallelism на время выполнения операции с индексами. Аргумент MAXDOP ограничивает число процессоров, используемых для выполнения параллельных планов. Максимальное число процессоров — 64.

    Аргумент max_degree_of_parallelism может принимать следующие значения:

    • 1
      Подавляет создание параллельного плана.
    • >1
      Ограничивает максимальное количество процессоров, используемых в параллельных операциях с индексами, заданным или меньшим числом в зависимости от текущей рабочей нагрузки системы.
    • 0 (по умолчанию)
      В зависимости от текущей рабочей нагрузки системы используется имеющееся или меньшее число процессоров.

    Дополнительные сведения см. в разделе Настройка параллельных индексных операций.

    ms188783.note(ru-ru,SQL.90).gifПримечание.
    Параллельные операции с индексами доступны только в выпуске SQL Server 2005 Enterprise Edition.

Замечания

Инструкция CREATE INDEX оптимизируется, как и любой другой запрос. Для уменьшения числа операций ввода-вывода обработчик запросов может вместо таблицы просматривать другой индекс. В некоторых ситуациях можно отказаться от операций сортировки. На многопроцессорных компьютерах с установленным сервером SQL Server 2005 Enterprise Edition инструкция CREATE INDEX, как и другие запросы, может использовать несколько процессоров для операций просмотра и сортировки, связанных с созданием индекса. Дополнительные сведения см. в разделе Настройка параллельных индексных операций.

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

Индексы могут создаваться для временной таблицы. При удалении таблицы или в конце сеанса такие индексы удаляются.

Индексы поддерживают дополнительные свойства. Дополнительные сведения см. в разделе Использование расширенных свойств с объектами базы данных.

Кластеризованные индексы

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

Уникальные индексы

Если существует уникальный индекс, то каждый раз при добавлении данных с помощью операции вставки компонент Database Engine делает проверку на появление повторяющихся значений. Производится откат операций вставки, которые могли бы создать повторяющиеся значения ключей, и компонент Database Engine выдает сообщение об ошибке. Это происходит даже в случае, если операция вставки изменяет несколько строк, а повторяющееся значение может появиться всего одно. Если делается попытка ввести данные, для которых существует уникальный индекс, и аргумент IGNORE_DUP_KEY имеет значение ON, сбоем завершаются операции только с теми строками, где нарушается свойство уникальности индекса. Дополнительные сведения об уникальных индексах см. в разделе Создание уникальных индексов.

Секционированные индексы

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

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

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

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

Индексированные представления

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

Для успешного создания индексированного представления сделайте следующие шаги.

  1. Убедитесь в правильности параметров SET для всех существующих таблиц, на которые ссылается представление.
  2. Прежде чем создавать новые таблицы и представление, проверьте параметры SET для сеанса.
  3. Проверьте, что определение представления детерминировано.
  4. Создайте представление с помощью WITH SCHEMABINDING.
  5. Создайте уникальный кластеризованный индекс для представления.

Обязательные параметры SET для индексированных представлений

Если при выполнении запроса активны разные параметры SET, оценка одного и того же выражения может дать разные результаты в компоненте Database Engine. Например, если параметр SET CONCAT_NULL_YIELDS_NULL равен ON, выражение 'abc' + NULL возвращает значение NULL. Но если параметр CONCAT_NULL_YIEDS_NULL равен OFF, то же самое выражение дает результат 'abc'.

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

  • Создается индексированное представление.

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

  • Индексированное представление используется оптимизатором запросов для создания плана запроса.

    Параметры SET Обязательное значение Значение сервера по умолчанию Значение OLE DB и ODBC по умолчанию Значение DB-Library по умолчанию

    ANSI_NULLS

    ON

    ON

    ON

    OFF

    ANSI_PADDING

    ON

    ON

    ON

    OFF

    ANSI_WARNINGS*

    ON

    ON

    ON

    OFF

    ARITHABORT

    ON

    ON

    OFF

    OFF

    CONCAT_NULL_YIELDS_NULL

    ON

    ON

    ON

    OFF

    NUMERIC_ROUNDABORT

    OFF

    OFF

    OFF

    OFF

    QUOTED_IDENTIFIER

    ON

    ON

    ON

    OFF

    Если в SQL Server 2005 задать параметру ANSI_WARNINGS значение ON, параметр ARITHABORT автоматически станет равным ON, если уровень совместимости базы данных равен 90. Если уровень совместимости базы данных равен 80 или ниже, необходимо явно установить параметр ARITHABORT в значение ON.

Если используется соединение с сервером через интерфейсы OLE DB или ODBC, достаточно изменить параметр ARITHABORT. Все значения DB-Library должны быть правильно установлены на уровне сервера с помощью хранимой процедуры sp_configure, или из приложения с помощью команды SET. Дополнительные сведения о параметрах SET см. в разделе Использование параметров в СУБД SQL Server.

ms188783.note(ru-ru,SQL.90).gifВажно!
Настоятельно рекомендуется присвоить пользовательскому параметру ARITHABORT значение ON на всем сервере, как только в какой-либо базе данных сервера будет создано первое индексированное представление или индекс на вычисляемом столбце.

Детерминированные функции

Определение индексированного представления должно быть детерминированным. Представление детерминировано, если детерминированы все выражения в списке выборки, а также в предложениях WHERE и GROUP BY. Детерминированные выражения возвращают одни и те же результаты при каждом вычислении с одним и тем же набором входных значений. Только детерминированные функции могут использоваться в детерминированных выражениях. Например, функция DATEADD детерминирована, т. к. всегда возвращает один и тот же результат для любого заданного набора значений аргументов трех ее параметров. Функция GETDATE не детерминирована, т. к. всегда вызывается с одним и тем же аргументом, но каждый раз возвращает разные значения. Дополнительные сведения см. в разделе Детерминированные и недетерминированные функции.

Даже если выражение детерминировано, если оно содержит выражения с плавающей запятой, результат может зависеть от архитектуры процессора или версии микропрограммы. Для сохранения целостности данных такие выражения могут быть только неключевыми столбцами индексированных представлений. Детерминированные выражения, не содержащие выражений с плавающей запятой, называются точными выражениями. Только точные детерминированные выражения могут содержаться в ключевых столбцах и предложениях WHERE или GROUP BY индексированных представлений.

Чтобы определить, является ли столбец представления детерминированным, используйте свойство IsDeterministic функции COLUMNPROPERTY. Чтобы определить, является ли точным детерминированный столбец в представлении с привязкой к схеме, используйте свойство IsPrecise функции COLUMNPROPERTY. Функция COLUMNPROPERTY возвращает значение 1 (если TRUE), 0 (если FALSE) и NULL в случае недопустимого входного значения. Это означает, что столбец не является детерминированным или точным.

Дополнительные требования

Кроме требований, касающихся параметров SET и детерминированных функций, должны выполняться следующие требования:

  • Пользователь, выполняющий инструкцию CREATE INDEX, должен быть владельцем представления.

  • Если определение представления содержит предложение GROUP BY, ключ уникального кластеризованного индекса может включать только столбцы, указанные в этом предложении.

  • Чтобы представление с привязкой к схеме могло ссылаться. ссылаться на базовые таблицы, эти базовые таблицы должны быть созданы с правильными значениями параметров SET.

  • Имя таблицы в определении представления должно состоять из двух частей: schema**.**tablename.

  • Пользовательские функции должны создаваться с аргументом WITH SCHEMABINDING.

  • Обращаться к пользовательским функциям необходимо по имени, состоящему из двух частей: schema**.**function.

  • Представления должны создаваться с аргументом WITH SCHEMABINDING.

  • В представлении допустимы ссылки только на базовые таблицы той же самой базы данных. Ссылки на другие представления недопустимы.

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

    функция COUNT(*);

    функция ROWSET

    производная таблица

    самосоединение

    DISTINCT

    STDEV, VARIANCE, AVG;

    Столбцы типа float*, text, ntext или image columns

    вложенный запрос

    полнотекстовые предикаты (CONTAIN, FREETEXT);

    функция SUM для выражения, которое может содержать NULL

    пользовательская статистическая функция CLR

    TOP

    MIN, MAX

    UNION.

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

Если присутствует предложение GROUP BY, определение VIEW должно содержать функцию COUNT_BIG(*) и не должно содержать предложения HAVING. Эти ограничения для предложения GROUP BY относятся только к определению индексированного представления. Запрос может использовать индексированное представление в своем плане выполнения, даже если он не соответствует этим ограничениям для предложения GROUP BY.

Индексированные представления могут создаваться на секционированной таблице и сами могут быть секционированными. Дополнительные сведения о секционировании см. в предыдущем разделе «Секционированные индексы».

Чтобы компонент Database Engine не использовал индексированные представления, включите в запрос подсказку OPTION (EXPAND VIEWS). Кроме того, если любой из вышеуказанных параметров установлен неправильно, оптимизатор не сможет использовать индексы представлений. Дополнительные сведения о подсказке OPTION (EXPAND VIEWS) см. в разделе SELECT (Transact-SQL).

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

XML-индексы

Дополнительные сведения см. в разделе Индексы для столбцов типа данных xml.

Размер ключа индекса

Максимальный размер ключа индекса равен 900 байт. Индексы на столбцах типа varchar, размер которых превышает 900 байт, могут быть созданы, если в момент создания индекса объем существующих данных в столбцах не превышает 900 байт; но последующие операции вставки или обновления, вызывающие превышение общего размера 900 байт, будут заканчиваться ошибкой. Дополнительные сведения см. в разделе Максимальный размер ключей индекса. Ключ кластеризованного индекса не может содержать столбцы типа varchar, у которых есть данные в единице размещения ROW_OVERFLOW_DATA. Если кластеризованный индекс создается на столбце типа varchar, и данные находятся в единице размещения IN_ROW_DATA, последующие операции вставки или обновления, которые могли бы вывести данные за границы строки, заканчиваются ошибкой. Дополнительные сведения о единицах размещения см. в разделе Организация таблиц и индексов.

В сервере SQL Server 2005 некластеризованные индексы могут включать неключевые столбцы на конечном уровне индекса. При вычислении размера ключа индекса компонент Database Engine эти столбцы не рассматривает. Дополнительные сведения см. в разделе Индекс с включенными столбцами.

Вычисляемые столбцы

Индексы могут создаваться на вычисляемых столбцах. В сервере SQL Server 2005 вычисляемые столбцы могут иметь свойство PERSISTED. Это значит, что компонент Database Engine хранит вычисленные значения в таблице и обновляет их при обновлении любых столбцов, от которых зависит вычисляемый столбец. Компонент Database Engine использует эти материализованные значения, когда создает индекс по столбцу и когда запрос обращается к индексу.

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

  • Вычисляемые столбцы, основанные на выражениях языка Transact-SQL, функциях CLR и методах пользовательских типов данных CLR, отмеченных пользователем как детерминированные.
  • Вычисляемые столбцы, основанные на выражениях, которые определены компонентом Database Engine как детерминированные, но не являются точными.

Для материализованных вычисляемых столбцов необходимо, чтобы следующие параметры SET имели значения, указанные выше в разделе «Обязательные параметры SET для индексированных представлений».

Ограничения UNIQUE или PRIMARY KEY могут содержать вычисляемый столбец, если он удовлетворяет всем условиям для индексирования. Вычисляемый столбец должен быть детерминированным и точным или детерминированным и материализованным. Дополнительные сведения о детерминизме см. в разделе Детерминированные и недетерминированные функции.

Вычисляемые столбцы, производные от типов данных image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml, могут индексироваться как ключевые или включенные неключевые столбцы, если тип данных вычисляемого столбца приемлем как тип данных для столбца ключа индекса или неключевого столбца. Например, нельзя создать первичный XML-индекс для вычисляемого столбца типа xml. Если размер ключа индекса превышает 900 байт, выдается предупреждение.

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

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

Если же после создания таблицы создать индекс на вычисляемом столбце c, та же инструкция INSERT будет заканчиваться ошибкой.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

Дополнительные сведения см. в разделе Создание индексов вычисляемых столбцов.

Включенные столбцы в индексах

Неключевые столбцы, называемые «включенными столбцами», могут добавляться на конечный уровень некластеризованного индекса для повышения производительности запроса благодаря тому, что индекс включает все необходимые данные для запроса. Т.е. все столбцы, указанные в запросе, включаются в индекс в качестве ключевых или неключевых столбцов. Таким образом оптимизатор запросов может найти все необходимые данные путем просмотра индекса, не обращаясь к данным таблицы или кластеризованного индекса. Дополнительные сведения см. в разделе Индекс с включенными столбцами.

Установка параметров индекса

В сервере SQL Server 2005 представлены новые параметры индексов и изменен способ установки параметров. Для обратной совместимости синтаксиса аргумент WITH option_name эквивалентен аргументу WITH (<option_name> = ON**)**. Устанавливая параметры индекса, необходимо соблюдать следующие правила.

  • Новые параметры индекса могут быть заданы только с помощью аргумента WITH (option_name= ON | OFF**)**.
  • Нельзя задавать параметры с помощью нового синтаксиса синтаксиса и совместимого старого в одной и той же инструкции. Например, инструкция с WITH (DROP_EXISTING, ONLINE = ON**)** вызовет ошибку.
  • При создании XML-индекса параметры должны указываться с помощью аргумента WITH (option_name= ON | OFF**)**.

Предложение DROP_EXISTING

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

Если индекс налагает ограничение PRIMARY KEY или UNIQUE и его определение никак не меняется, он удаляется и создается вновь с сохранением существующих ограничений. Но если изменить определение индекса, инструкция вызовет ошибку. Чтобы изменить ограничение PRIMARY KEY или UNIQUE, удалите ограничение и добавьте ограничение вместе с новым определением.

Предложение DROP_EXISTING повышает производительность, если заново создается кластеризованный индекс с тем же самым или другим набором ключей на таблице, имеющей также некластеризованные индексы. Предложение DROP_EXISTING заменяет удаление старого кластеризованного индекса с помощью инструкции DROP INDEX и последующее создание нового кластеризованного индекса с помощью инструкции CREATE INDEX. Некластеризованные индексы перестраиваются один раз, а после этого только в случае, если меняется определение индекса. Предложение DROP_EXISTING не перестраивает некластеризованные индексы, если определение индекса содержит то же самое имя индекса, ключевые столбцы, столбцы секционирования, атрибут уникальности и порядок сортировки, что и исходный индекс.

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

Предложение DROP_EXISTING не сортирует данные заново, если те же столбцы ключа индекса используются в том же порядке с тем же порядком сортировки по возрастанию или убыванию, за исключением случаев, когда инструкция индекса задает некластеризованный индекс и аргумент ONLINE равен OFF. Если кластеризованный индекс отключен, операция CREATE INDEX WITH DROP_EXISTING должна выполняться с аргументом ONLINE в значении OFF. Если некластеризованный индекс отключен и не связан с отключенным кластеризованным индексом, операция CREATE INDEX WITH DROP_EXISTING может выполняться с аргументом ONLINE в значении OFF или ON.

Если удаляются или перестраиваются индексы с 128 или более экстентами, компонент Database Engine откладывает фактическое освобождение страниц и связанных с ними блокировок до фиксации транзакции. Дополнительные сведения см. в разделе Удаление и повторная сборка больших объектов.

Аргумент ONLINE

Следующие правила применяются к операциям с индексами в фоновом режиме.

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

Дополнительные сведения см. в разделе Оперативное выполнение операций над индексом.

Параметры блокировок строк и страниц

Если заданы аргументы ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при обращении к индексу разрешены блокировки на уровне строк, страниц и таблиц. Компонент Database Engine выбирает соответствующую блокировку и может повышать уровень блокировки с уровня строк или страниц до уровня таблицы. Дополнительные сведения см. в разделе Укрупнение блокировки (компонент Database Engine).

Если заданы аргументы ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при обращении к индексу разрешены только блокировки на уровне таблиц.

Дополнительные сведения о настройке степени гранулярности блокировок для индекса см. в разделе Настройка блокировки индекса.

Просмотр сведений об индексах

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

Разрешения

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

Примеры

A. Создание простого некластеризованного индекса

В следующем примере создается некластеризованный индекс по столбцу VendorID таблицы Purchasing.ProductVendor .

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (VendorID); 
GO

Б. Создание простого некластеризованного составного индекса

В следующем примере создается некластеризованный составной индекс по столбцам SalesQuota и SalesYTD таблицы Sales.SalesPerson.

USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

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

В следующем примере создается уникальный некластеризованный индекс по столбцу Name таблицы Production.UnitMeasure. Индекс требует уникальности данных, вставляемых в столбец Name.

USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

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

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

В результате выдается сообщение об ошибке:

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

Г. Использование аргумента IGNORE_DUP_KEY

В следующем примере демонстрируется влияние аргумента IGNORE_DUP_KEY со значениями ON и OFF на операцию вставки нескольких строк во временную таблицу. В таблицу #Test вставляется одна строка, которая намеренно приведет к появлению повторяющихся значений при выполнении второй многострочной операции вставки INSERT. Счетчик строк таблицы возвращает количество вставленных строк.

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Ниже приведены результаты второй инструкции INSERT.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows 
-------------- 
38

Обратите внимание, что строки из таблицы Production.UnitMeasure, не нарушающие ограничение уникальности, были успешно вставлены. Было выдано предупреждение и строка с повторяющимся значением не была вставлена, но отката всей транзакции не произошло.

Те же инструкции выполняются вновь, но теперь с аргументом IGNORE_DUP_KEY, равным OFF.

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

Ниже приведены результаты второй инструкции INSERT.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows 
-------------- 
1

Обратите внимание, что ни одна из строк таблицы Production.UnitMeasure не была вставлена, хотя ограничение индекса UNIQUE было нарушено только одной строкой.

Д. Использование предложения DROP_EXISTING для удаления и повторного создания индекса

В следующем примере индекс по столбцу ProductID таблицы Production.WorkOrder удаляется и создается вновь с помощью предложения DROP_EXISTING. Указываются также аргументы FILLFACTOR и PAD_INDEX.

USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

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

В следующем примере создаются представление и индекс этого представления. Включено два запроса, использующих созданное индексированное представление.

USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

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

В следующем примере создается некластеризованный индекс с одним ключевым столбцом (PostalCode) и четырьмя неключевыми столбцами (AddressLine1, AddressLine2, City, StateProvinceID). Далее следует запрос, все данные для которого есть в индексе. Прежде чем выводить индекс, выбранный оптимизатором запросов, выберите в меню Запрос среды SQL Server Management Studio команду Показать действительный план выполнения.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

З. Создание первичного XML-индекса

В следующем примере создается первичный XML-индекс по столбцу CatalogDescription таблицы Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.indexes
            WHERE name = N'PXML_ProductModel_CatalogDescription')
    DROP INDEX PXML_ProductModel_CatalogDescription 
        ON Production.ProductModel;
GO
CREATE PRIMARY XML INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel (CatalogDescription);
GO

И. Создание вторичного XML-индекса

В следующем примере создается вторичный XML-индекс по столбцу CatalogDescription таблицы Production.ProductModel.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IXML_ProductModel_CatalogDescription_Path')
    DROP INDEX IXML_ProductModel_CatalogDescription_Path
        ON Production.ProductModel;
GO
CREATE XML INDEX IXML_ProductModel_CatalogDescription_Path 
    ON Production.ProductModel (CatalogDescription)
    USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;
GO

К. Создание секционированного индекса

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

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_TransactionHistory_ReferenceOrderID')
    DROP INDEX IX_TransactionHistory_ReferenceOrderID
        ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
ON Production.TransactionHistory (ReferenceOrderID)
ON TransactionsPS1 (TransactionDate);
GO

См. также

Справочник

ALTER INDEX (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE STATISTICS (Transact-SQL)
CREATE TABLE (Transact-SQL)
Типы данных (Transact-SQL)
DBCC SHOW_STATISTICS (Transact-SQL)
DROP INDEX (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)
sys.xml_indexes (Transact-SQL)
EVENTDATA (Transact-SQL)

Другие ресурсы

Определение требований к месту на диске для индексов
Общие рекомендации по проектированию индексов
Индексы для столбцов типа данных xml
Архитектура таблиц и индексов

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений

Версия Журнал

14 апреля 2006 г.
Обновленное содержимое
  • Удалены уникальные некластеризованные индексы из списка индексов, которые не могут быть созданы в фоновом режиме. Это изменение относится к SQL Server 2005 с пакетом обновления 1 (SP1) и более поздними версиями.
  • Под таблицей «Параметры настройки» добавлено примечание о том, как параметр ANSI_WARNINGS в значении ON влияет на параметр ARITHABORT.

5 декабря 2005 г.
Новое содержимое
  • В список индексов добавлены уникальные некластеризованные индексы, которые не могут быть созданы в фоновом режиме.