ALTER INDEX (Transact-SQL)

index_name

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

ALL

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

Указание ALL с этой операцией	Отказывает, если в таблице имеется один или несколько
REBUILD WITH ONLINE = ON	XML-индекс Пространственный индекс Столбцы типов данных больших объектов: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml
REBUILD PARTITION = partition_number	Несекционированный, пространственный, отключенный индекс или XML-индекс
REORGANIZE	Индексы с параметром ALLOW_PAGE_LOCKS, равным OFF
REORGANIZE PARTITION = partition_number	Несекционированный, пространственный, отключенный индекс или XML-индекс
IGNORE_DUP_KEY = ON	Пространственный индекс XML-индекс
ONLINE = ON	Пространственный индекс XML-индекс

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

database_name

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

schema_name

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

table_or_view_name

Имя таблицы или представления, связанного с индексом. Чтобы отобразить отчет по индексам объекта, следует воспользоваться представлением каталога sys.indexes.

REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]

Указывает, что индекс будет перестроен с использованием тех же столбцов, типов индекса, атрибута уникальности и порядка сортировки. Это предложение эквивалентно DBCC DBREINDEX. REBUILD включает отключенный индекс. При перестройке кластеризованного индекса не перестраиваются ассоциированные некластеризованные индексы, если только не указано ключевое слово ALL. Если параметры индекса не заданы, то применяется существующий параметр индекса, который хранится в таблице sys.indexes. Для любого параметра индекса, значение которого не хранится в таблице sys.indexes, применяется значение по умолчанию, указанное в определении аргумента.

При перестроении XML-индекса или пространственного индекса параметры ONLINE = ON и IGNORE_DUP_KEY = ON недопустимы.

Если указано ключевое слово ALL, а базовая таблица реализована в виде кучи, операция перестроения не воздействует на таблицу. Перестраиваются все некластеризованные индексы, ассоциированные с таблицей.

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

Примечание
При перестроении первичного XML-индекса индексированная пользовательская таблица недоступна в течение действия операции с индексами.

PARTITION

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

PARTITION = ALL, перестроение всех секций.

partition_number

Номер секции секционированного индекса, который предстоит перестроить или реорганизовать partition_number, — константное выражение, которое может ссылаться на переменные. К ним относятся переменные определяемого пользователем типа или функции и определяемые пользователем функции, но нельзя ссылаться на инструкцию языка Transact-SQL. Должен существовать partition_number , или выполнение инструкции завершится с ошибкой.

WITH (<single_partition_rebuild_index_option>)

SORT_IN_TEMPDB, MAXDOP и DATA_COMPRESSION — параметры, которые могут быть указаны при перестроении одиночной секции (PARTITION = n). XML-индексы не могут быть указаны в операции перестроения одиночной секции.

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

DISABLE

Помечает индекс как отключенный и недоступный для использования компонентом Database Engine. Любой индекс может быть отключен. Определение отключенного индекса остается в системном каталоге без базовых индексных данных. Отключение кластеризованного индекса блокирует доступ пользователя к данным базовой таблицы. Чтобы активировать индекс, следует использовать инструкцию ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING. Дополнительные сведения см. в разделе Отключение индексов.

REORGANIZE

Указывает, что конечный уровень индекса будет реорганизован. Это предложение эквивалентно DBCC INDEXDEFRAG. Инструкция ALTER INDEX REORGANIZE всегда выполняется в режиме в сети. Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE. REORGANIZE не может быть указана для отключенного индекса или индекса с ALLOW_PAGE_LOCKS со значением OFF.

WITH ( LOB_COMPACTION = { ON | OFF } )

Указывает, что все страницы, содержащие данные большого объекта (LOB), сжимаются. Типы данных LOB: image, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml. Сжатие этих данных может освободить место на диске. Значение по умолчанию — ON.

ON

Все страницы, содержащие данные большого объекта, сжимаются.

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

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

OFF

Все страницы, содержащие данные большого объекта, не сжимаются.

Параметр OFF не влияет на кучу.

Предложение LOB_COMPACTION пропускается, если отсутствуют столбцы LOB.

SET ( <set_index option> [ ,... n] )

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

PAD_INDEX = { ON | OFF }

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

ON

Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.

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

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

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

FILLFACTOR = fillfactor

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

Примечание
Значения коэффициентов заполнения 0 и 100 идентичны.

Явный параметр FILLFACTOR применяется, только если индекс создается впервые или перестраивается. Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

Для просмотра коэффициента заполнения используйте таблицу sys.indexes.

Важно!
Создание или замена кластеризованного индекса со значением FILLFACTOR влияет на пространство памяти, занимаемое данными, так как компонент Database Engine перераспределяет данные при создании кластеризованного индекса.

SORT_IN_TEMPDB = { ON | OFF }

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

ON

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

OFF

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

Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается.

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

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.

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

ONLINE = { ON | OFF }

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

Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.

Примечание
Операции с индексами в сети доступны только в выпусках SQL Server Enterprise, Developer и Evaluation.

ON

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

OFF

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

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

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

• XML-индексы

• индексы локальных временных таблиц;

• Подмножество секционированного индекса (секционированный индекс можно целиком перестроить в сети).

• кластеризованные индексы, если базовая таблица содержит типы данных LOB;

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

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

ALLOW_ROW_LOCKS = { ON | OFF }

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

ON

Блокировки строк допустимы при доступе к индексу. Необходимость в блокировке строк определяет компонент Database Engine.

OFF

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

ON

Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке страниц определяет компонент Database Engine.

OFF

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

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

MAXDOP =max_degree_of_parallelism

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

Важно!
Хотя параметр MAXDOP синтаксически поддерживается для всех индексов XML, для пространственного или первичного XML-индекса инструкция ALTER INDEX в настоящее время использует только один процессор.

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

1

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

>1

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

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

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

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

Примечание
Параллельные операции с индексами доступны только в выпусках SQL Server Developer, Evaluation и Enterprise.

DATA_COMPRESSION

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

NONE

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

ROW

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

PAGE

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

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

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

Указывает секции, к которым применяется параметр 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)
)

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

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

Примечание
Значения для параметров ONLINE, MAXDOP и SORT_IN_TEMPDB не хранятся в системном каталоге. Если значение некоторого параметра не указано в инструкции индекса, то используется значение по умолчанию.

В компьютерах с несколькими процессорами инструкция ALTER INDEX REBUILD, как и другие запросы, использует больше процессоров для операций просмотра и сортировки, связанных с изменением индекса. При выполнении инструкции ALTER INDEX REORGANIZE, без предложения LOB_COMPACTION или с ним, значение аргумента max degree of parallelism представляет собой однопотоковую операцию. Дополнительные сведения см. в разделе Настройка параллельных операций с индексами.

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

Восстановление индексов

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

Примечание

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

В предыдущих версиях SQL Server иногда можно было перестроить некластеризованный индекс, чтобы исправить любые несоответствия, вызванные отказами оборудования. В SQL Server 2008 по-прежнему можно скорректировать такие несоответствия между индексом и кластеризованным индексом, перестроив некластеризованный индекс в режиме вне сети. Однако нельзя устранить несоответствия некластеризованного индекса, перестроив индекс в режиме в сети, потому что механизм перестроения в этом режиме будет использовать существующий некластеризованный индекс в качестве основы для перестроения и тем самым закрепит несоответствие. При перестроении индекса вне сети, в отличие от вышесказанного, принудительно запускается просмотр кластеризованного индекса (или кучи) и в результате устраняются несоответствия. В предыдущих версиях рекомендованным методом устранения несоответствий было восстановление неправильных данных из резервных копий, однако исправить несоответствия индекса можно, перестроив некластеризованный индекс в режиме «вне сети». Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).

Реорганизация индексов

Отключение индексов

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

Операция с индексами в сети

Ограничения пространственного индекса

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

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

А. Перестроение индекса

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

Б. Перестроение всех индексов по таблице и указание параметров

USE AdventureWorks2008R2;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

В. Реорганизация индекса со сжатием LOB

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

Г. Назначение параметров для индекса

USE AdventureWorks2008R2;
GO
ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

Д. Отключение индекса

USE AdventureWorks2008R2;
GO
ALTER INDEX IX_Employee_OrganizationNode ON HumanResources.Employee
DISABLE ;
GO

Е. Снятие ограничений

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
DISABLE ;
GO

Ж. Активация ограничений

USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department
REBUILD ;
GO

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

З. Перестройка секционированного индекса

USE AdventureWorks2008R2;
GO
-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5;
GO

И. Изменение настроек сжатия индекса

ALTER INDEX IX_INDEX1 
ON T1
REBUILD 
WITH ( DATA_COMPRESSION = PAGE )
GO

Справочник

Основные понятия