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
). Чтобы удалить кластеризованный индекс и изменить схему секционирования, необходимо выполнить следующие шаги.
Удалить кластеризованный индекс.
Измените таблицу с помощью
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
C. Удаление кластеризованного индекса в сети и настройка параметра 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