DROP INDEX (Transact-SQL)

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

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

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

Внимание

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

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

Синтаксис

Синтаксис SQL Server (все параметры, кроме файловой группы и файлового потока, применяются к База данных SQL Azure).

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

Синтаксис для База данных SQL Azure.

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 }

Синтаксис для Системы платформы Azure Synapse Analytics и Analytics (PDW).

DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

Аргументы

IF EXISTS

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

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

index_name

Имя удаляемого индекса.

database_name

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

schema_name

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

table_or_view_name

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

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

База данных SQL Azure поддерживает формат имени трех частей: database_name.[schema_name].object_name когда database_name находится текущая база данных или tempdb database_name, а object_name начинается с#.

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

Переопределяет параметр конфигурации максимальной степени параллелизма на время выполнения операции с индексами. Дополнительные сведения см. в разделе "Настройка максимальной степени параллелизма" (параметр конфигурации сервера). Используется 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.

• ON: долгосрочные блокировки таблиц не хранятся. Это позволяет продолжать выполнение запросов и обновлений базовых таблиц.

• OFF: применяются блокировки таблиц, а таблица недоступна во время операции индекса.

Этот 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.

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.

Замечания

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

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево 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.

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

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

XML-индексы

Параметры нельзя указывать при удалении индекса anXML. Кроме того, нельзя использовать <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 .

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

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

В следующем примере индекс IX_ProductVendor_BusinessEntityID ProductVendor в таблице удаляется в базе данных 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

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

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

Область применения: 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
• sys.tables
• sys.filegroups
• sp_spaceused