DROP INDEX (Transact-SQL)

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics AnalyticsPlatform System (PDW)

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

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

Важно!

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

Соглашения о синтаксисе Transact-SQL

Синтаксис

-- Syntax for SQL Server (All options except filegroup and filestream apply to Azure SQL Database.)  
  
DROP INDEX [ IF EXISTS ]   
{ <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.table_or_view_name | schema_name.table_or_view_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" } ]  
}  
-- Syntax for Azure SQL Database  
  
DROP INDEX  
{ <drop_relational_or_xml_or_spatial_index> [ ,...n ]   
}  
  
<drop_relational_or_xml_or_spatial_index> ::=   
    index_name ON <object>  
  
<object> ::=   
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }  
-- Syntax for Azure Synapse Analytics and Parallel Data Warehouse  
  
DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

Примечание.

Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.

Аргументы

IF EXISTS
Применимо к: SQL Server (SQL Server 2016 (13.x) до текущей версии.

Условное удаление индекса только в том случае, если он уже существует.

index_name
Имя индекса, который необходимо удалить.

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

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

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

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

База данных SQL Azure поддерживает формат трехкомпонентного имени имя_базы_данных.[имя_схемы].имя_объекта, если имя_базы_данных — это текущая база данных или tempdb, а имя_объекта начинается с символа #.

<drop_clustered_index_option>
Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

Управляет параметрами кластеризованного индекса. Эти параметры неприменимы к другим типам индексов.

MAXDOP = max_degree_of_parallelism
Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL (только уровни производительности P2 и P3).

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

Важно!

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

Параметр max_degree_of_parallelism может иметь одно из следующих значений:

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

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

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

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

Примечание.

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

ONLINE = ON | OFF
Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL Azure.

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

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

ВЫКЛ.
Применяются блокировки таблиц, при этом таблицы становятся недоступны на время выполнения индексирования.

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

Примечание.

Операции с индексами в сети недоступны в каждом выпуске SQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022.

MOVE TO { partition_scheme_name(column_name) | filegroup_name | "default"
Применимо: SQL Server 2008 (10.0.x) и более поздних версий. База данных SQL поддерживает "default" в качестве имени файловой группы.

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

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

partition_scheme_name(column_name)
Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

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

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

filegroup_name
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

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

Примечание.

В этом контексте 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" }
Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

Определяет папку, в которую будет перемещаться таблица 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).

Замечания

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

Примечание.

В документации по SQL Server термин "сбалансированное дерево" обычно используется в отношении индексов. В индексах rowstore SQL Server реализует B+-дерево. Это не относится к индексам columnstore или хранилищам данных в памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

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

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

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

Если удаляются индексы со 128 или более экстентами, компонент ядра СУБД откладывает действительное освобождение страниц и связанных с ними блокировок до фиксации транзакции.

Иногда индексы удаляются и пересоздаются для реорганизации или перестроения индекса, например чтобы применить новое значение коэффициента заполнения, или для реорганизации данных после массовой загрузки. Для этих задач более эффективно использование инструкции 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 или более поздней версии, DROP INDEX может использовать больше процессоров для выполнения операций сканирования и сортировки, связанных с удалением кластеризованного индекса, как и другие запросы. Можно вручную настроить число процессоров, применяемых для запуска инструкции DROP INDEX, указав параметр индекса MAXDOP. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.

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

  1. Удалить кластеризованный индекс.

  2. Изменение таблицы с помощью ALTER TABLE ... ПЕРЕСТРОИТЬ... параметр, указывающий параметр сжатия.

При удалении кластеризованного индекса в режиме не в сети удаляются только верхние уровни кластеризованных индексов, следовательно, операция выполняется довольно быстро. При удалении кластеризованного индекса в режиме "в сети" 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_VendorIDProductVendor в таблице удаляется в базе данных AdventureWorks2022.

DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO  

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

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

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

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

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

Область применения: SQL Server 2008 (10.0.x) и более поздних версий База данных SQL.

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

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

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

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

--Create a clustered index on the PRIMARY filegroup if the index does not exist.  
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 AdventureWorks2022  
        ADD FILEGROUP NewGroup;  
    ALTER DATABASE AdventureWorks2022  
        ADD FILE (NAME = File1,  
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.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. Если бы они были, необходимо было бы сначала удалить их.

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

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

Следующий пример удаляет XML-индекс в ProductModel таблице в базе данных AdventureWorks2022.

DROP INDEX PXML_ProductModel_CatalogDescription   
    ON Production.ProductModel;  

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

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

Применимо: SQL Server 2008 (10.0.x) и более поздних версий.

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

См. также

ALTER INDEX (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL)
Инструкция ALTER TABLE (Transact-SQL)
Инструкция CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.indexes (Transact-SQL)
sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL)
sp_spaceused (Transact-SQL)