DROP INDEX (Transact-SQL)

项目
05/17/2024

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW)

从当前数据库中删除一个或多个关系索引、空间索引、筛选索引或 XML 索引。可以通过指定 MOVE TO 选项来删除聚集索引并将生成的表移到单个事务中的另一个文件组或分区方案。

该 DROP INDEX 语句不适用于通过定义 PRIMARY KEY 或 UNIQUE 约束创建的索引。若要删除约束和相应的索引，请使用 ALTER TABLE 和 DROP CONSTRAINT 子句。

重要

将在 SQL Server 的未来版本中删除定义的 <drop_backward_compatible_index> 语法。请避免在新的开发工作中使用该功能，并考虑修改当前使用该功能的应用程序。请改用在 <drop_relational_or_xml_or_spatial_index> 下指定的语法。无法使用后向兼容的语法删除 XML 索引。

Transact-SQL 语法约定

语法

SQL Server 的语法（文件组和文件流以外的所有选项都适用于Azure SQL 数据库）。

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

Azure SQL 数据库的语法。

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 和分析平台系统的语法（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 目录视图。

Azure SQL 数据库支持三部分名称格式：database_name.[schema_name].object_name当database_name当前数据库或database_name为tempdb时，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）。

在索引操作期间替代 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）及更高版本，Azure SQL 数据库。

指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认为 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 树这个术语。在行存储索引中，数据库引擎实现了 B+ 树。这不适用于列存储索引或内存优化表上的索引。有关详细信息，请参阅 SQL Server 以及 Azure SQL 索引体系结构和设计指南。

如果索引所在的文件组处于脱机状态或设置为只读，则无法删除索引。

删除索引视图的聚集索引时，将自动删除同一视图上所有非聚集索引和自动创建统计信息。不会删除手动创建的统计信息。

保留语法 <table_or_view_name>.<index_name> 以实现向后兼容性。无法使用向后兼容的语法删除 XML 索引或空间索引。

删除带有 128 个或更多区数的索引时，数据库引擎将延迟实际页释放及其关联的锁，直到提交事务为止。

有时，需要删除并重新创建索引以重新组织或重新生成索引，例如在大容量加载之后应用新的填充因子值或重新组织数据。为此，使用 ALTER INDEX 更高效，尤其是对于聚集索引。 ALTER INDEX REBUILD 具有优化以防止重新生成非聚集索引的开销。

将选项与 DROP INDEX 配合使用

删除聚集索引时，可以设置以下索引选项： MAXDOPONLINE和 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列的NULLname目录视图中。若要查看表名，请使用 on object_id进行sys.tables联接sys.indexes。有关示例查询的信息，请参阅示例 D。

在运行 SQL Server 2005 Enterprise Edition 或更高版本的多处理器计算机上， DROP INDEX 可能会使用更多处理器来执行与删除聚集索引相关联的扫描和排序操作，就像其他查询一样。可以通过指定MAXDOP索引选项来手动配置用于运行语句的DROP INDEX处理器数。有关详细信息，请参阅配置并行索引操作。

删除聚集索引时，除非修改了分区方案，否则相应的堆分区将保留其数据压缩设置。如果分区方案已更改，则所有分区将重新生成为未压缩的状态（DATA_COMPRESSION = NONE）。若要删除聚集索引并更改分区方案，需要执行以下两个步骤：

删除聚集索引。
使用 ALTER TABLE ... REBUILD ... 指定压缩选项的选项修改表。

删除 OFFLINE聚集索引时，只会删除聚集索引的上限;因此，操作速度很快。删除 ONLINE聚集索引后，SQL Server 将重新生成堆两次，一次用于步骤 1，一次用于步骤 2。有关数据压缩的详细信息，请参阅数据压缩。

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 固定数据库角色授予此权限。

示例

本文中的 Transact-SQL 代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可从 Microsoft SQL Server 示例和社区项目主页下载它。

A. 删除索引

以下示例删除 AdventureWorks2022 数据库中 ProductVendor 表上的索引 IX_ProductVendor_BusinessEntityID。

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

E. 联机删除 PRIMARY KEY 约束

由于DROP INDEX创建PRIMARY KEY或UNIQUE约束而创建的索引无法使用。使用语句删除 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 索引

以下示例删除 AdventureWorks2022 数据库中 ProductModel 表上的 XML 索引。

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. 删除 FILESTREAM 表上的聚集索引

以下示例使用 MyPartitionScheme 子句和 MOVE TO 子句联机删除聚集索引并将生成的表（堆）和 FILESTREAM 数据移至 FILESTREAM ON 分区方案。

适用于：SQL Server 2008 (10.0.x) 及更高版本。

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

通过

DROP INDEX (Transact-SQL)

语法

参数

IF EXISTS

index_name

database_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” }

partition_scheme_name ( column_name )

filegroup_name

"default"

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name |“default” }

partition_scheme_name

filestream_filegroup_name

"default"

注解

将选项与 DROP INDEX 配合使用

XML 索引

空间索引

权限

示例

A. 删除索引

B. 删除多个索引

°C 联机删除聚集索引并设置 MAXDOP 选项

D. 联机删除聚集索引并将表移动到新文件组

E. 联机删除 PRIMARY KEY 约束

F. 删除 XML 索引

G. 删除 FILESTREAM 表上的聚集索引

反馈

其他资源

通过

DROP INDEX (Transact-SQL)

语法

参数

IF EXISTS

index_name

database_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” }

partition_scheme_name ( column_name )

filegroup_name

"default"

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name |“default” }

partition_scheme_name

filestream_filegroup_name

"default"

注解

将选项与 DROP INDEX 配合使用

XML 索引

空间索引

权限

示例

A. 删除索引

B. 删除多个索引

°C 联机删除聚集索引并设置 MAXDOP 选项

D. 联机删除聚集索引并将表移动到新文件组

E. 联机删除 PRIMARY KEY 约束

F. 删除 XML 索引

G. 删除 FILESTREAM 表上的聚集索引

相关内容

反馈

其他资源