Статья
09/28/2010

DROP INDEX (Transact-SQL)

Инструкция DROP INDEX удаляет индексы из текущей базы данных SQL Server. Чтобы удалить индекс с помощью обозревателя объектов, см. Как удалить индекс (среда SQL Server Management Studio).

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

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

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

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

Примечание

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

Примечание

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

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

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

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

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.

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

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

DROP INDEX spatial_index_name ON spatial_table_name;

Дополнительные сведения о пространственных индексах см. в разделе Работа с пространственными индексами (компонент Database Engine).

Разрешения

Для выполнения инструкции 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

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

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

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    MOVE TO MyPartitionScheme
    FILESTREAM_ON MyPartitionScheme;
GO

См. также

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