DROP INDEX (Transact-SQL)

Статья
12/15/2008

Важно!
Синтаксис, определяемый в `<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-индексы нельзя удалить с использованием обратно совместимого синтаксиса.

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

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

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

Синтаксис

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

<drop_relational_or_xml_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" 
            }
}

Аргументы

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. Параметр MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимально допустимое количество — 64 процессора.

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

Примечание.

Параллельные операции c индексами доступны только в выпуске SQL Server 2005 Enterprise Edition.

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

ONLINE = ON | OFF
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
- ON
  Не устанавливаются долгосрочные блокировки таблицы. Это позволяет продолжать выполнение запросов и обновлений базовых таблиц.
- OFF
  Применяются блокировки таблиц, при этом таблицы становятся недоступны на время выполнения индексирования.
Параметр ONLINE можно указать только при удалении кластеризованных индексов. Дополнительные сведения см. в разделе «Примечания».

Примечание.

Фоновые операции с индексами доступны только в выпуске SQL Server 2005 Enterprise Edition.

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

MOVE TO
Определяет размещение, куда будут перемещаться строки данных, находящиеся на конечном уровне кластеризованного индекса. Данные перемещаются в новое расположение со структурой типа куча. В качестве нового расположения можно указать файловую группу или схему секционирования, но они должны уже существовать. Параметр 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).

Замечания

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

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

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

Синтаксис index_name ON { table_or_view_name } изменился в SQL Server 2005. Синтаксис table_or_view_name**.**index_name сохраняется для обратной совместимости. При совмещении обоих параметров в одной транзакции произойдет ошибка при выполнении инструкции. XML-индекс нельзя удалить с использованием обратно совместимого синтаксиса.

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

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

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

В SQL Server 2005 при удалении кластеризованного индекса можно устанавливать следующие параметры: 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. Пример запроса см. в примере Г.

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

XML-индексы

При удалении XML-индекса нельзя указывать параметры. При удалении первичного XML-индекса все связанные вторичные XML-индексы удаляются автоматически. Дополнительные сведения см. в разделе Индексы для столбцов типа данных xml.

Разрешения

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

Примеры

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

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

USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

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

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

USE AdventureWorks;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_VendorAddress_AddressID ON Purchasing.VendorAddress;
GO

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

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

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

USE AdventureWorks;
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 AdventureWorks;
GO
--Create a clustered index on the PRIMARY filegroup if it 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.
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the ALTER DATABASE statement 
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks
        ADD FILEGROUP NewGroup;
    EXECUTE ('ALTER DATABASE AdventureWorks
        ADD FILE (NAME = File1,
            FILENAME = '''+ @data_path + '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 AdventureWorks;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.ProductCostHistory
    DROP CONSTRAINT PK_ProductCostHistory_ProductID_StartDate
        WITH (ONLINE = ON);
GO

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

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

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

См. также