Продажи: 1-800-867-1389
Информация
Запрошенная вами тема показана ниже. Однако эта тема не включена в библиотеку.

CREATE INDEX (Transact-SQL)

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

Примечание Примечание

Поскольку Microsoft База данных SQL Windows Azure не поддерживает таблицы кучи, таблица должна иметь кластеризованный индекс. Если таблица создана без кластеризованного ограничения, необходимо создать кластеризованный индекс, прежде чем разрешать операции вставки на таблице.

Дополнительные сведения о создании XML-индексов см. в разделе CREATE XML INDEX (Transact-SQL). Дополнительные сведения о создании пространственного индекса см. в разделе CREATE SPATIAL INDEX (Transact-SQL). Сведения о создании оптимизированного для памяти xVelocity индекса columnstore см. в разделе CREATE COLUMNSTORE INDEX (Transact-SQL).

Применимо для следующих объектов: SQL Server (SQL Server 2008 по текущую версию), База данных SQL Windows Azure (С первоначального выпуска по текущий выпуск).

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

-- SQL Server Syntax

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
    [ ON { partition_scheme_name ( column_name ) 
         | filegroup_name 
         | default 
         }
    ]
    [ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

[ ; ]

<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 }
  | STATISTICS_INCREMENTAL = { ON | OFF }
  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
  | ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | MAXDOP = max_degree_of_parallelism
  | DATA_COMPRESSION = { NONE | ROW | PAGE} 
     [ ON PARTITIONS ( { <partition_number_expression> | <range> } 
     [ , ...n ] ) ]
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::= 
        column_name IN (constant ,...n)

<comparison> ::= 
        column_name <comparison_op> constant 

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

<range> ::= 
<partition_number_expression> TO <partition_number_expression>


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 
}
-- Windows Azure SQL Database Syntax 

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
    ON <object> ( column [ ASC | DESC ] [ ,...n ] ) 
    [ INCLUDE ( column_name [ ,...n ] ) ]
    [ WHERE <filter_predicate> ]
    [ WITH ( <relational_index_option> [ ,...n ] ) ]
[ ; ]

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

<relational_index_option> ::=
{
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }

  | DROP_EXISTING = { ON | OFF }
  | ONLINE = { ON | OFF }
}

<filter_predicate> ::= 
    <conjunct> [ AND <conjunct> ]

<conjunct> ::=
    <disjunct> | <comparison>

<disjunct> ::= 
        column_name IN (constant ,…)

<comparison> ::= 
        column_name <comparison_op> constant 

<comparison_op> ::=
    { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< }

UNIQUE

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

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

CLUSTERED

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

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

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

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

Примечание Примечание

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

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

NONCLUSTERED

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

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

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

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

index_name

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

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 ] )

Указывает неключевые столбцы, добавляемые на конечный уровень некластеризованного индекса. Некластеризованный индекс может быть уникальным или неуникальным.

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

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

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

Дополнительные сведения о создании XML-индекса см. в разделе CREATE XML INDEX (Transact-SQL).

WHERE <filter_predicate>

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

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

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

WHERE StartDate > '20000101' AND EndDate <= '20000630'

WHERE ComponentID IN (533, 324, 753)

WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Отфильтрованные индексы не применяются к XML-индексам и полнотекстовым индексам. Для индексов UNIQUE только выбранные строки должны иметь уникальные значения индексов. Отфильтрованные индексы не поддерживают параметр IGNORE_DUP_KEY.

ON partition_scheme_name(column_name)

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

Примечание Примечание

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

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

Примечание Примечание

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

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

ON filegroup_name

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

ON "default"

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

Аргумент filestream_filegroup_name — имя файловой группы FILESTREAM. В файловой группе должен быть определен один файл для файловой группы с помощью инструкции CREATE DATABASE или ALTER DATABASE, иначе возникает ошибка.

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

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

Предложение FILESTREAM_ON NULL может быть указано в инструкции CREATE INDEX, если создается кластеризованный индекс и таблица не содержит столбец FILESTREAM.

Дополнительные сведения см. в разделе FILESTREAM (SQL Server).

<object>::=

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

database_name

Имя базы данных.

schema_name

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

table_or_view_name

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

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

В База данных SQL Windows Azure поддерживается трехкомпонентный формат имен database_name.[schema_name].object_name, если database_name — текущая база данных или database_name представляет собой tempdb, а object_name начинается с #.

<relational_index_option>::=

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

PAD_INDEX = { ON | OFF }

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

Определяет разреженность индекса. Значение по умолчанию — OFF.

ON

Процент свободного места, определяемый параметром fillfactor, применяется к страницам индекса промежуточного уровня.

OFF или fillfactor не указан

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

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

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

FILLFACTOR =fillfactor

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

Определяет величину в процентах, показывающую, насколько компонент Компонент Database Engine должен заполнять конечный уровень каждой страницы индекса во время его создания или перестроения. Значение fillfactor должно быть целым числом от 1 до 100. Если параметр fillfactor равен 100, то компонент Компонент Database Engine создаст индексы с полностью заполненными страницами конечного уровня.

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

Важное примечание Важно!

Создание кластеризованного индекса с аргументом FILLFACTOR меньше 100 влияет на объем пространства хранения, занимаемого данными, т. к. компонент Компонент Database Engine перераспределяет данные, когда создает кластеризованный индекс.

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

SORT_IN_TEMPDB = { ON | OFF }

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

ON

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

OFF

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

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

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

IGNORE_DUP_KEY = { ON | OFF }

Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Параметр не работает во время выполнения инструкции CREATE INDEX, ALTER INDEX или UPDATE. Значение по умолчанию — OFF.

ON

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

OFF

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

IGNORE_DUP_KEY нельзя установить в значение ON для индексов, создаваемых для представлений, неуникальных индексов, XML-индексов, пространственных индексов и фильтруемых индексов.

Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

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

STATISTICS_NORECOMPUTE = { ON | OFF}

Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

ON

Устаревшие статистики не пересчитываются автоматически.

OFF

Автоматическое обновление статистических данных включено.

Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE.

Важное примечание Важно!

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

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

STATISTICS_INCREMENTAL = { ON | OFF }

При значении ON статистики создаются как статистики отдельно по секциям. При значении OFF дерево статистик удаляется и SQL Server повторно вычисляет статистики. По умолчанию используется значение OFF.

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

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

  • Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.

  • Статистики, созданные в базах данных, доступных только для чтения.

  • Статистики, созданные по фильтрованным индексам.

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

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

  • Статистики, созданные с пространственными индексами или XML-индексами.

Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

DROP_EXISTING = { ON | OFF }

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

Указывает, что названный существующий кластеризованный или некластеризованный индекс удаляется и перестраивается. Значение по умолчанию — OFF.

ON

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

OFF

Выдается ошибка, если индекс с указанным именем уже существует.

Тип индекса не может быть изменен с помощью аргумента DROP_EXISTING.

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

ONLINE = { ON | OFF }

Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

Примечание Примечание

Операции с индексами в сети доступны не во всех выпусках Microsoft SQL Server. Перечень функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Функции, поддерживаемые различными выпусками SQL Server 2014.

ON

Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это включает запросы или обновления применительно к обрабатываемой базовой таблице и индексам. В начале операции совмещаемая блокировка (S) удерживается на исходном объекте в течение очень короткого времени. В конце операции на источнике на короткое время удерживается совмещаемая блокировка (S), если создается некластеризованный индекс. Если в режиме в сети создается или удаляется кластеризованный индекс и, если перестраивается кластеризованный или некластеризованный индекс, удерживается блокировка SCH-M (изменения схемы). При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.

OFF

Блокировки таблиц применяются во время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT.

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

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

  • XML-индекс

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

  • Исходные уникальные кластеризованные индексы представлений.

  • Отключенные кластеризованные индексы.

  • Кластеризованные индексы, если базовая таблица содержит типы данных LOB: image, ntext, text и пространственные типы.

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

ALLOW_ROW_LOCKS = { ON | OFF }

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

ON

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

OFF

Блокировки строк не используются.

ALLOW_PAGE_LOCKS = { ON | OFF }

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

ON

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

OFF

Блокировки страниц не используются.

MAXDOP = max_degree_of_parallelism

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

1

Подавляет формирование параллельных планов.

>1

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

0 (по умолчанию)

В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

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

Примечание Примечание

Параллельные операции с индексами доступны не во всех выпусках Microsoft SQL Server. Перечень функций, поддерживаемых выпусками SQL Server, см. в разделе Функции, поддерживаемые различными выпусками SQL Server 2014.

DATA_COMPRESSION

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

NONE

Индекс или заданные секции не сжимаются.

ROW

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

PAGE

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

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

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,...n ] )

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

Указывает секции, к которым применяется параметр DATA_COMPRESSION. Если индекс не секционирован, аргумент ON PARTITIONS создаст ошибку. Если не указано предложение ON PARTITIONS, то параметр DATA_COMPRESSION применяется ко всем секциям секционированного индекса.

<Выражение_номера_секции> можно указать одним из следующих способов.

  • Указав номер секции, например ON PARTITIONS (2).

  • Указав номера нескольких секций, разделив их запятыми, например ON PARTITIONS (1, 5).

  • Указав диапазоны секций и отдельные секции, например ON PARTITIONS (2, 4, 6 TO 8).

<Диапазон> можно указать номерами секций, разделенными ключевым словом TO, например: ON PARTITIONS (6 TO 8).

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

REBUILD WITH 
(
DATA_COMPRESSION = NONE ON PARTITIONS (1), 
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8), 
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

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

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

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

Индексы поддерживают расширенные свойства.

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

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

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

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

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

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

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

Предупреждение Внимание!

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

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

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

Статистические данные в SQL Server 2014 не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

Отфильтрованные индексы

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

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

Параметры SET в столбце Required Value необходимы в любом из следующих случаев.

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

  • Операция INSERT, UPDATE, DELETE или MERGE изменяет данные в отфильтрованном индексе.

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

    Параметры SET

    Обязательное значение

    ANSI_NULLS

    ON

    ANSI_PADDING

    ON

    ANSI_WARNINGS*

    ON

    ARITHABORT

    ON

    CONCAT_NULL_YIELDS_NULL

    ON

    NUMERIC_ROUNDABORT

    OFF

    QUOTED_IDENTIFIER

    ON

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

Если параметры SET неверны, может произойти следующее.

  • Отфильтрованный индекс не будет создан.

  • Компонент Компонент Database Engine сформирует ошибку и выполнит откат любой инструкции INSERT, UPDATE, DELETE или MERGE, которая изменила значения данных в индексе.

  • Оптимизатор запросов не учтет индекс в плане выполнения любой инструкции Transact-SQL.

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

Пространственные индексы

Дополнительные сведения о пространственных индексах см. в разделах CREATE SPATIAL INDEX (Transact-SQL) и Общие сведения о пространственных индексах.

XML-индексы

Дополнительные сведения об XML-индексах см. в разделе CREATE XML INDEX (Transact-SQL) и XML-индексы (SQL Server).

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

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

Некластеризованные индексы могут включать неключевые столбцы на конечном уровне индекса. При вычислении размера ключа индекса компонент Компонент Database Engine эти столбцы не рассматривает. Дополнительные сведения см. в разделе Создание индексов с включенными столбцами.

Примечание Примечание

Если ключевые столбцы секционирования не представлены в неуникальном кластеризованном индексе при секционировании таблиц, то они добавляются в индекс службами Компонент Database Engine. Объединенный размер индексированных столбцов (без учета включенных столбцов) и любых добавленных столбцов секционирования в неуникальном кластеризованном индексе не может превышать 1 800 байт.

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

Индексы могут создаваться на вычисляемых столбцах. Кроме того, вычисляемые столбцы могут иметь свойство 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 выбирает соответствующую блокировку и может повышать уровень с блокировки строки или страницы до блокировки таблицы.

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

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

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

Сжатие данных

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

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

  • Неконечные страницы индекса не сжаты на уровне страниц, но могут быть сжаты на уровне строк.

  • У каждого некластеризованного индекса индивидуальные настройки сжатия, которые не наследуются от базовой таблицы.

  • При создании кластеризованного индекса в куче кластеризованный индекс наследует состояние сжатия кучи, если не указано другое состояние сжатия.

На секционированные индексы налагаются следующие ограничения.

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

  • Инструкция ALTER INDEX <index> ... Инструкция REBUILD PARTITION ... производит перестроение указанной секции индекса.

  • Инструкция ALTER INDEX <index> ... Инструкция REBUILD WITH ... производит перестроение всех секций индекса.

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

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

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

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

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);

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

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

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 в базе данных AdventureWorks2012. Индекс требует уникальности данных, вставляемых в столбец Name.

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);

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

--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. Счетчик строк таблицы возвращает количество вставленных строк.

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.

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 в базе данных AdventureWorks2012 с использованием параметра DROP_EXISTING. Указываются также аргументы FILLFACTOR и PAD_INDEX.

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
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 команду Показать действительный план выполнения.

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

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

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

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

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

IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

К.Создание сжатого индекса

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

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

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

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

Следующий пример демонстрирует создание индекса для секционированной таблицы с помощью сжатия страниц для секции 1 индекса и сжатия строк для секций индекса со 2 по 4.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO
Была ли вам полезна эта информация?
(1500 символов осталось)
Спасибо за ваш отзыв

Добавления сообщества

Показ:
© 2014 Microsoft