DROP INDEX (Transact-SQL)

Статья
06/18/2013

Удаляет один или несколько реляционных, пространственных, фильтруемых или XML-индексов из текущей базы данных. Можно удалить кластеризованный индекс и переместить полученную в результате таблицу в другую файловую группу или схему секционирования в одной транзакции, указав параметр MOVE TO.

Инструкция DROP INDEX неприменима к индексам, созданным при указании ограничений параметров PRIMARY KEY и UNIQUE. Для удаления ограничения и соответствующего индекса используется инструкция ALTER TABLE с предложением DROP CONSTRAINT.

Важно!
Синтаксис, определяемый в <drop_backward_compatible_index>, не будет поддерживаться в будущих версиях Microsoft SQL Server. Избегайте использования этого синтаксиса в новых разработках и учитывайте необходимость изменения в будущем приложений, использующих эти функции сейчас. Используйте синтаксис, описанный в <drop_relational_or_xml_index>. XML-индексы нельзя удалить с использованием обратно совместимого синтаксиса.

Синтаксис, определяемый в <drop_backward_compatible_index>, не будет поддерживаться в будущих версиях Microsoft SQL Server. Избегайте использования этого синтаксиса в новых разработках и учитывайте необходимость изменения в будущем приложений, использующих эти функции сейчас. Используйте синтаксис, описанный в <drop_relational_or_xml_index>. XML-индексы нельзя удалить с использованием обратно совместимого синтаксиса.

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

Синтаксис

DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_or_spatial_index> ::=
    index_name ON <object> 
    [ WITH ( <drop_clustered_index_option> [ ,...n ] ) ]

<drop_backward_compatible_index> ::=
    [ owner_name. ] table_or_view_name.index_name 

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

<drop_clustered_index_option> ::=
{
    MAXDOP = max_degree_of_parallelism
  | ONLINE = { ON | OFF }
  | MOVE TO { partition_scheme_name ( column_name ) 
            | filegroup_name
            | "default" 
            }
  [ FILESTREAM_ON { partition_scheme_name 
            | filestream_filegroup_name 
            | "default" } ]
}

Аргументы

index_name
Имя индекса, который необходимо удалить.
database_name
Имя базы данных.
schema_name
Имя схемы, к которой принадлежит таблица или представление.
table_or_view_name
Имя таблицы или представления, связанного с индексом. Пространственные индексы поддерживаются только для таблиц.

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

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

Важно!
Параметр MAXDOP нельзя использовать для пространственных или XML-индексов.

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

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

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

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

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

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

Параметр ONLINE можно указать только при удалении кластеризованных индексов. Дополнительные сведения см. в разделе «Примечания».

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

MOVE TO { partition_scheme_name**(column_name)** | filegroup_name | "default"
Определяет размещение, куда будут перемещаться строки данных, находящиеся на конечном уровне кластеризованного индекса. Данные перемещаются в новое расположение со структурой типа куча. В качестве нового расположения можно указать файловую группу или схему секционирования, но они должны уже существовать. Параметр MOVE TO недопустим для индексированных представлений и некластеризованных индексов. Если ни схема секционирования, ни файловая группа не указаны, результирующая таблица помещается в схему секционирования или файловую группу, которая определена для кластеризованного индекса.

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

partition_scheme_name ( column_name )
Указывает схему секционирования, в которой будет размещена результирующая таблица. Схема секционирования должна быть создана заранее выполнением инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. Если размещение не указано и таблица секционирована, таблица включается в ту же схему секционирования, где размещен существующий кластеризованный индекс.

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

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

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

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

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }
Определяет папку, в которую будет перемещаться таблица FILESTREAM, находящаяся на конечном уровне кластеризованного индекса. Данные перемещаются в новое расположение со структурой типа куча. В качестве нового расположения можно указать файловую группу или схему секционирования, но они должны уже существовать. Параметр FILESTREAM ON недопустим для индексированных представлений или некластеризованных индексов. Если не указана схема секционирования, то данные будут размещены в той же схеме секционирования или файловой группе, которая была определена для кластеризованного индекса.

partition_scheme_name
Указывает схему секционирования для данных FILESTREAM. Схема секционирования должна быть создана заранее выполнением инструкции CREATE PARTITION SCHEME или ALTER PARTITION SCHEME. Если размещение не указано и таблица секционирована, таблица включается в ту же схему секционирования, где размещен существующий кластеризованный индекс.

При указании схемы секционирования для инструкции MOVE TO необходимо использовать ту же схему секционирования, что и для инструкции FILESTREAM ON.
filestream_filegroup_name
Указывает файловую группу FILESTREAM для данных FILESTREAM. Если расположение не указано, а таблица не секционирована, данные включаются в файловую группу FILESTREAM по умолчанию.

"default"
Указывает расположение по умолчанию для данных FILESTREAM.

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

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

Замечания

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

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

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

Синтаксис table_or_view_name.index_name сохраняется для обратной совместимости. Пространственный или XML-индекс нельзя удалить с использованием синтаксиса обратной совместимости.

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

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

Использование параметров инструкции DROP INDEX

При удалении кластеризованного индекса можно установить следующие параметры: MAXDOP, ONLINE и MOVE TO.

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

При присвоении параметру ONLINE значения ON запросы и изменения базовых данных и связанных некластеризованных индексов не блокируются во время выполнения транзакции DROP INDEX. В режиме в сети одновременно может удаляться только один кластеризованный индекс. Полное описание параметра ONLINE приведено в разделе CREATE INDEX (Transact-SQL).

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

Использование параметров ONLINE = ON и MOVE TO требует дополнительного временного места на диске.

После удаления индекса результирующая куча появляется в представлении каталога sys.indexes со значением NULL в столбце name. Для просмотра имени таблицы выполните соединение sys.indexes с sys.tables по object_id. Пример запроса см. в примере Г.

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

При удалении кластеризованного индекса соответствующие секции кучи сохраняют настройки сжатия данных, если только не была изменена схема секционирования. Если схема секционирования подверглась изменениям, все секции перестраиваются в распакованное состояние (DATA_COMPRESSION = NONE). Чтобы удалить кластеризованный индекс и изменить схему секционирования, необходимо выполнить следующие шаги.

Удалить кластеризованный индекс.
Изменить таблицу с помощью инструкции ALTER TABLE ... Параметр REBUILD ... определяющий степень сжатия.

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

XML-индексы

При удалении XML-индекса нельзя указывать параметры. Также невозможно использование синтаксиса table_or_view_name**.**index_name. При удалении первичного XML-индекса все связанные вторичные XML-индексы удаляются автоматически. Дополнительные сведения см. в разделе XML-индексы (SQL Server).

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

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

DROP INDEX spatial_index_name ON spatial_table_name;

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

Разрешения

Для выполнения инструкции DROP INDEX как минимум требуется разрешение ALTER для таблицы или представления. По умолчанию это разрешение предоставляется предопределенной роли сервера sysadmin и предопределенным ролям баз данных db_ddladmin и db_owner.

Примеры

А.Удаление индекса

Следующий пример иллюстрирует удаление индекса IX_ProductVendor_VendorID в таблице ProductVendor.

USE AdventureWorks2012;
GO
DROP INDEX IX_ProductVendor_BusinessEntityID 
    ON Purchasing.ProductVendor;
GO

Б.Удаление нескольких индексов

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

USE AdventureWorks2012;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_Address_StateProvinceID ON Person.Address;
GO

В.Удаление кластеризованного индекса в режиме в сети и установка параметра MAXDOP

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

Примечание
Этот пример можно выполнить только в выпуске SQL Server 2005 Enterprise Edition или более позднем.

USE AdventureWorks2012;
GO
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO

Г.Удаление кластеризованного индекса в режиме в сети и перемещение таблицы в другую файловую группу

В следующем примере кластеризованный индекс удаляется в режиме в сети и результирующая таблица (куча) перемещается в файловую группу NewGroup с использованием предложения MOVE TO. Представления каталога sys.indexes, sys.tables и sys.filegroups опрашиваются для проверки размещения индексов и таблиц в файловых группах до и после перемещения.

USE AdventureWorks2012;
GO
--Create a clustered index on the PRIMARY filegroup if the index does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
            N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
    CREATE UNIQUE CLUSTERED INDEX
        AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials (ProductAssemblyID, ComponentID, 
        StartDate)
    ON 'PRIMARY';
GO
-- Verify filegroup location of the clustered index.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
    i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
    JOIN sys.tables as t ON i.object_id = t.object_id
        AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U')
GO
--Create filegroup NewGroup if it does not exist.
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks2012
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2012
        ADD FILE (NAME = File1,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\File1.ndf')
        TO FILEGROUP NewGroup;
    END
GO
--Verify new filegroup
SELECT * from sys.filegroups;
GO
-- Drop the clustered index and move the BillOfMaterials table to
-- the Newgroup filegroup.
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials 
    WITH (ONLINE = ON, MOVE TO NewGroup);
GO
-- Verify filegroup location of the moved table.
SELECT t.name AS [Table Name], i.name AS [Index Name], i.type_desc,
    i.data_space_id, f.name AS [Filegroup Name]
FROM sys.indexes AS i
    JOIN sys.filegroups AS f ON i.data_space_id = f.data_space_id
    JOIN sys.tables as t ON i.object_id = t.object_id
        AND i.object_id = OBJECT_ID(N'Production.BillOfMaterials','U');
GO

Д.Удаление ограничения PRIMARY KEY в режиме в сети

Индексы, созданные в результате создания ограничений параметров PRIMARY KEY или UNIQUE, нельзя удалить с помощью инструкции DROP INDEX. Они удаляются с помощью инструкции ALTER TABLE DROP CONSTRAINT. Дополнительные сведения см. в разделе ALTER TABLE.

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

USE AdventureWorks2012;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.TransactionHistoryArchive
DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
WITH (ONLINE = ON);

GO

Е.Удаление XML-индекса

В следующем примере удаляется XML-индекс в таблице ProductModel.

USE AdventureWorks2012;
GO
DROP INDEX PXML_ProductModel_CatalogDescription 
    ON Production.ProductModel;
GO

Ж.Удаление кластеризованного индекса для таблицы FILESTREAM

В следующем примере кластеризованный индекс удаляется в режиме в сети и результирующая таблица (куча) вместе с данными FILESTREAM перемещается в схему секционирования MyPartitionScheme с использованием предложений MOVE TO и FILESTREAM ON.

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    WITH (MOVE TO MyPartitionScheme,
          FILESTREAM_ON MyPartitionScheme);
GO