DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

从当前数据库中删除一个或多个关系索引、空间索引、筛选索引或 XML 索引。Removes one or more relational, spatial, filtered, or XML indexes from the current database. 通过指定 MOVE TO 选项,可以在单个事务中删除聚集索引并将生成的表移动到另一个文件组或分区方案。You can drop a clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option.

DROP INDEX 语句不适用于通过定义 PRIMARY KEY 或 UNIQUE 约束创建的索引。The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. 若要删除该约束和相应的索引,请使用带有 DROP CONSTRAINT 子句的 ALTER TABLETo remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.

重要

MicrosoftMicrosoftSQL ServerSQL Server 的将来版本将删除在 <drop_backward_compatible_index> 中定义的语法。The syntax defined in <drop_backward_compatible_index> will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 请避免在新的开发工作中使用该功能,并考虑修改当前使用该功能的应用程序。Avoid using this syntax in new development work, and plan to modify applications that currently use the feature. 请改用在 <drop_relational_or_xml_index> 下指定的语法。Use the syntax specified under <drop_relational_or_xml_index> instead. 使用向后兼容语法无法删除 XML 索引。XML indexes cannot be dropped using backward compatible syntax.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- 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 SQL Data Warehouse and Parallel Data Warehouse  
  
DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

参数Arguments

IF EXISTS IF EXISTS
适用范围:SQL ServerSQL ServerSQL Server 2016 (13.x)SQL Server 2016 (13.x)当前版本) 。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

仅当索引已存在时对其进行有条件地删除。Conditionally drops the index only if it already exists.

index_name index_name
要删除的索引名称。Is the name of the index to be dropped.

database_namedatabase_name
数据库的名称。Is the name of the database.

schema_nameschema_name
表或视图所属架构的名称。Is the name of the schema to which the table or view belongs.

table_or_view_name table_or_view_name
与该索引关联的表或视图的名称。Is the name of the table or view associated with the index. 只有表支持空间索引。Spatial indexes are supported only on tables.

若要显示对象的索引报表,请使用 sys.indexes 目录视图。To display a report of the indexes on an object, use the sys.indexes catalog view.

Azure SQL 数据库支持由三部分组成的名称格式 database_name.[schema_name].object_name,其中 database_name 为当前数据库,database_name 为 tempdb,object_name 以 # 开头。Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

<drop_clustered_index_option><drop_clustered_index_option>
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

控制聚集索引选项。Controls clustered index options. 这些选项不能与其他索引类型一起使用。These options cannot be used with other index types.

MAXDOP = max_degree_of_parallelism MAXDOP = max_degree_of_parallelism
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL Database(仅限性能级别 P2 和 P3) 。Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database (Performance Levels P2 and P3 only).

在索引操作期间替代 max degree of parallelism 配置选项 。Overrides the max degree of parallelism configuration option for the duration of the index operation. 有关详细信息,请参阅 配置 max degree of parallelism 服务器配置选项For more information, see Configure the max degree of parallelism Server Configuration Option. 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大数量为 64 个处理器。The maximum is 64 processors.

重要

空间索引或 XML 索引不允许使用 MAXDOP。MAXDOP is not allowed for spatial indexes or XML indexes.

max_degree_of_parallelism 可以是 :max_degree_of_parallelism can be:

11
取消生成并行计划。Suppresses parallel plan generation.

>1>1
将并行索引操作中使用的最大处理器数量限制为指定数量。Restricts the maximum number of processors used in a parallel index operation to the specified number.

0(默认值)0 (default)
根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。Uses the actual number of processors or fewer based on the current system workload.

有关详细信息,请参阅 配置并行索引操作For more information, see Configure Parallel Index Operations.

备注

并非在 SQL ServerSQL Server 的每个版本中均提供并行索引操作。Parallel index operations are not available in every edition of SQL ServerSQL Server. 有关 SQL ServerSQL Server 各版本支持的功能列表,请参阅 SQL Server 2016 的版本和支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

ONLINE = ON | OFF ONLINE = ON | OFF
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, Azure SQL DatabaseAzure SQL Database.

指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. 默认为 OFF。The default is OFF.

ONON
不保留长期表锁。Long-term table locks are not held. 这样便允许继续对基础表进行查询或更新。This allows queries or updates to the underlying table to continue.

OFFOFF
应用表锁,该表在索引操作期间不可用。Table locks are applied and the table is unavailable for the duration of the index operation.

只能在删除聚集索引时指定 ONLINE 选项。The ONLINE option can only be specified when you drop clustered indexes. 有关详细信息,请参见“备注”部分。For more information, see the Remarks section.

备注

SQL ServerSQL Server的各版本中均不提供联机索引操作。Online index operations are not available in every edition of SQL ServerSQL Server. 有关 SQL ServerSQL Server 各版本支持的功能列表,请参阅 SQL Server 2016 的版本和支持的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | " default "MOVE TO { partition_scheme_name(column_name) | filegroup_name | " default "
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017. SQL 数据库SQL Database 支持将 "default" 作为文件组名称。supports "default" as the filegroup name.

指定一个位置,以移动当前处于聚集索引叶级别的数据行。Specifies a location to move the data rows that currently are in the leaf level of the clustered index. 数据将以堆的形式移动到这一新位置。The data is moved to the new location in the form of a heap. 可以将分区方案或文件组指定为新位置,但该分区方案或文件组必须已存在。You can specify either a partition scheme or filegroup as the new location, but the partition scheme or filegroup must already exist. MOVE TO 对索引视图或非聚集索引无效。MOVE TO is not valid for indexed views or nonclustered indexes. 如果未指定分区方案或文件组,则生成的表将位于为聚集索引定义的同一分区方案或文件组中。If a partition scheme or filegroup is not specified, the resulting table will be located in the same partition scheme or filegroup as was defined for the clustered index.

如果使用 MOVE TO 删除了聚集索引,则将重新生成所有对基表的非聚集索引,但这些索引会保留在其原始文件组或分区方案中。If a clustered index is dropped by using MOVE TO, any nonclustered indexes on the base table are rebuilt, but they remain in their original filegroups or partition schemes. 如果基表移动到其他文件组或分区方案中,这些非聚集索引不会通过移动来与基表(堆)的新位置一致。If the base table is moved to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the base table (heap). 因此,即使非聚集索引以前与聚集索引对齐,它们也可能不再与堆对齐。Therefore, even if the nonclustered indexes were previously aligned with the clustered index, they might no longer be aligned with the heap. 有关已分区索引的详细信息,请参阅已分区表和已分区索引For more information about partitioned index alignment, see Partitioned Tables and Indexes.

partition_scheme_name ( column_name ) partition_scheme_name ( column_name )
适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

指定分区方案作为生成表的位置。Specifies a partition scheme as the location for the resulting table. 该分区方案必须已通过执行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME 创建完毕。The partition scheme must have already been created by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. 如果未指定位置但表已分区,则表将包含在与现有聚集索引相同的分区方案中。If no location is specified and the table is partitioned, the table is included in the same partition scheme as the existing clustered index.

方案中的列名不限制为索引定义中的列。The column name in the scheme is not restricted to the columns in the index definition. 可以指定基表中的任何列。Any column in the base table can be specified.

filegroup_name filegroup_name
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指定文件组作为生成表的位置。Specifies a filegroup as the location for the resulting table. 如果未指定位置并且表是未分区的,则生成的表将包含在与聚集索引相同的文件组中。If no location is specified and the table is not partitioned, the resulting table is included in the same filegroup as the clustered index. 该文件组必须已存在。The filegroup must already exist.

"default" " default "
指定生成表的默认位置。Specifies the default location for the resulting table.

备注

在此上下文中,default 不是关键字。In this context, default is not a keyword. 它是默认文件组的标识符,必须对其进行分隔,就像在 MOVE TO "default" 或 MOVE TO [default] 中一样 。It is an identifier for the default filegroup and must be delimited, as in MOVE TO " default " or MOVE TO [ default ]. 如果指定了“default”,则必须将当前会话的 QUOTED_IDENTIFIER 选项设置为 ON 。If " default " is specified, the QUOTED_IDENTIFIER option must be set ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" } FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | " default " }
适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

指定一个位置,当前处于聚集索引叶级别的 FILESTREAM 表将移至此位置。Specifies a location to move the FILESTREAM table that currently is in the leaf level of the clustered index. 数据将以堆的形式移动到这一新位置。The data is moved to the new location in the form of a heap. 可以将分区方案或文件组指定为新位置,但该分区方案或文件组必须已存在。You can specify either a partition scheme or filegroup as the new location, but the partition scheme or filegroup must already exist. FILESTREAM ON 对于索引视图或非聚集索引无效。FILESTREAM ON is not valid for indexed views or nonclustered indexes. 如果未指定分区方案,则数据将位于为聚集索引定义的同一分区方案中。If a partition scheme is not specified, the data will be located in the same partition scheme as was defined for the clustered index.

partition_scheme_name partition_scheme_name
指定 FILESTREAM 数据的分区方案。Specifies a partition scheme for the FILESTREAM data. 该分区方案必须已通过执行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME 创建完毕。The partition scheme must have already been created by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. 如果未指定位置但表已分区,则表将包含在与现有聚集索引相同的分区方案中。If no location is specified and the table is partitioned, the table is included in the same partition scheme as the existing clustered index.

如果为 MOVE TO 指定了分区方案,则必须为 FILESTREAM ON 使用同一个分区方案。If you specify a partition scheme for MOVE TO, you must use the same partition scheme for FILESTREAM ON.

filestream_filegroup_name filestream_filegroup_name
指定 FILESTREAM 数据的 FILESTREAM 文件组。Specifies a FILESTREAM filegroup for FILESTREAM data. 如果未指定位置并且表未分区,则数据将包括在默认 FILESTREAM 文件组中。If no location is specified and the table is not partitioned, the data is included in the default FILESTREAM filegroup.

"default" " default "
指定 FILESTREAM 数据的默认位置。Specifies the default location for the FILESTREAM data.

备注

在此上下文中,default 不是关键字。In this context, default is not a keyword. 它是默认文件组的标识符,必须对其进行分隔,就像在 MOVE TO "default" 或 MOVE TO [default] 中一样 。It is an identifier for the default filegroup and must be delimited, as in MOVE TO " default " or MOVE TO [ default ]. 如果指定了 "default",则当前会话的 QUOTED_IDENTIFIER 选项必须为 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 这是默认设置。This is the default setting. 有关详细信息,请参阅 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

RemarksRemarks

删除非聚集索引时,将从元数据中删除索引定义,并从数据库文件中删除索引数据页(B 树)。When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files. 删除聚集索引时,将从元数据中删除索引定义,并且存储于聚集索引叶级别的数据行将存储到生成的未排序表(堆)中。When a clustered index is dropped, the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in the resulting unordered table, a heap. 将重新获得以前由索引占有的所有空间。All the space previously occupied by the index is regained. 此后可将该空间用于任何数据库对象。This space can then be used for any database object.

如果索引所在的文件组脱机或设置为只读,则不能删除该索引。An index cannot be dropped if the filegroup in which it is located is offline or set to read-only.

删除索引视图的聚集索引时,将自动删除同一视图的所有非聚集索引和自动创建的统计信息。When the clustered index of an indexed view is dropped, all nonclustered indexes and auto-created statistics on the same view are automatically dropped. 手动创建的统计信息不会删除。Manually created statistics are not dropped.

保留 table_or_view_name . index_name 语法是为了保持后向兼容性。The syntax table_or_view_name.index_name is maintained for backward compatibility. XML 索引或空间索引无法使用向后兼容的语法删除。An XML index or spatial index cannot be dropped by using the backward compatible syntax.

删除带有 128 个或更多区数的索引时,数据库引擎Database Engine将延迟实际页释放及其关联的锁,直到提交事务为止。When indexes with 128 extents or more are dropped, the 数据库引擎Database Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

有时,需要删除并重新创建索引以重新组织或重新生成索引,例如在大容量加载之后应用新的填充因子值或重新组织数据。Sometimes indexes are dropped and re-created to reorganize or rebuild the index, such as to apply a new fill factor value or to reorganize data after a bulk load. 若要执行该操作,使用 ALTER INDEX 更为有效,尤其是对于聚集索引而言。To do this, using ALTER INDEXis more efficient, especially for clustered indexes. ALTER INDEX REBUILD 具有优化功能,可避免重新生成非聚集索引所造成的开销。ALTER INDEX REBUILD has optimizations to prevent the overhead of rebuilding the nonclustered indexes.

使用带选项的 DROP INDEXUsing Options with DROP INDEX

删除聚集索引时,可以设置以下索引选项:MAXDOP、ONLINE 和 MOVE TO。You can set the following index options when you drop a clustered index: MAXDOP, ONLINE, and MOVE TO.

使用 MOVE TO 删除聚集索引并将生成表移动到一个事务中的另一个文件组或分区方案。Use MOVE TO to drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction.

当指定 ONLINE = ON 时,DROP INDEX 事务不会阻塞对基础数据和关联非聚集索引的修改。When you specify ONLINE = ON, queries and modifications to the underlying data and associated nonclustered indexes are not blocked by the DROP INDEX transaction. 一次只能联机删除一个聚集索引。Only one clustered index can be dropped online at a time. 有关 ONLINE 选项的完整说明,请参阅 CREATE INDEX (Transact-SQL)For a complete description of the ONLINE option, see CREATE INDEX (Transact-SQL).

如果聚集索引在视图上被禁用,或者它在叶级别数据行中包含 text、ntext、image、varchar(max)、nvarchar(max)、varbinary(max) 或 xml 列,则不能联机删除该索引 。You cannot drop a clustered index online if the index is disabled on a view, or contains text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or xml columns in the leaf-level data rows.

使用 ONLINE = ON 和 MOVE TO 选项要求附加的临时磁盘空间。Using the ONLINE = ON and MOVE TO options requires additional temporary disk space.

删除索引后,生成的堆将出现在 sys.indexes 目录视图中,且 name 列中为 NULL 。After an index is dropped, the resulting heap appears in the sys.indexes catalog view with NULL in the name column. 若要查看表名,请将 sys.indexes 和 sys.tables 联接在 object_id 上 。To view the table name, join sys.indexes with sys.tables on object_id. 有关示例查询的信息,请参阅示例 D。For an example query, see example D.

在运行 SQL Server 2005 Enterprise EditionSQL Server 2005 Enterprise Edition 或更高版本的多处理器计算机中,DROP INDEX 可以使用更多处理器执行与删除聚集索引有关的扫描和排序操作,就像其他查询所做的一样。On multiprocessor computers that are running SQL Server 2005 Enterprise EditionSQL Server 2005 Enterprise Edition or later, DROP INDEX may use more processors to perform the scan and sort operations associated with dropping the clustered index, just like other queries do. 可以通过指定 MAXDOP 索引选项手动配置用于运行 DROP INDEX 语句的处理器数目。You can manually configure the number of processors that are used to run the DROP INDEX statement by specifying the MAXDOP index option. 有关详细信息,请参阅 配置并行索引操作For more information, see Configure Parallel Index Operations.

删除聚集索引时,除非修改了分区方案,否则相应的堆分区将保留其数据压缩设置。When a clustered index is dropped, the corresponding heap partitions retain their data compression setting unless the partitioning scheme is modified. 如果分区方案已更改,则所有分区都将重新生成为未压缩状态 (DATA_COMPRESSION = NONE)。If the partitioning scheme is changed, all partitions are rebuilt to an uncompressed state (DATA_COMPRESSION = NONE). 若要删除聚集索引并更改分区方案,需要执行以下两个步骤:To drop a clustered index and change the partitioning scheme requires the following two steps:

  1. 删除聚集索引。Drop the clustered index.

  2. 通过使用指定压缩选项的 ALTER TABLE ...REBUILD ... 选项来修改表。Modify the table by using an ALTER TABLE ... REBUILD ... option specifying the compression option.

脱机删除聚集索引时,只删除较高级别的聚集索引;因此,该操作的执行速度很快。When a clustered index is dropped OFFLINE, only the upper levels of clustered indexes are removed; therefore, the operation is quite fast. 如果联机删除聚集索引,SQL ServerSQL Server 将重新生成堆两次,一次针对步骤 1,一次针对步骤 2。When a clustered index is dropped ONLINE, SQL ServerSQL Server rebuilds the heap two times, once for step 1 and once for step 2. 有关数据压缩的详细信息,请参阅数据压缩For more information about data compression, see Data Compression.

XML 索引XML Indexes

删除 XML 索引时,不能指定选项。Options cannot be specified when you drop anXML index. 此外,不能使用 table_or_view_name . index_name 语法。Also, you cannot use the table_or_view_name.index_name syntax. 删除主 XML 索引时,将自动删除所有关联的辅助 XML 索引。When a primary XML index is dropped, all associated secondary XML indexes are automatically dropped. 有关详细信息,请参阅 XML 索引 (SQL Server)For more information, see XML Indexes (SQL Server).

空间索引Spatial Indexes

只有表支持空间索引。Spatial indexes are supported only on tables. 删除空间索引时,无法指定任何选项或使用 . index_nameWhen you drop a spatial index, you cannot specify any options or use .index_name. 正确的语法如下:The correct syntax is as follows:

DROP INDEX spatial_index_name ON spatial_table_name; DROP INDEX spatial_index_name ON spatial_table_name;

有关空间索引的详细信息,请参阅空间索引概述For more information about spatial indexes, see Spatial Indexes Overview.

权限Permissions

若要执行 DROP INDEX,至少需要对表或视图拥有 ALTER 权限。To execute DROP INDEX, at a minimum, ALTER permission on the table or view is required. 默认情况下,将向 sysadmin 固定服务器角色以及 db_ddladmindb_owner 固定数据库角色授予此权限。This permission is granted by default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles.

示例Examples

A.A. 删除索引Dropping an index

以下示例删除 AdventureWorks2012AdventureWorks2012 数据库中 ProductVendor 表上的索引 IX_ProductVendor_VendorIDThe following example deletes the index IX_ProductVendor_VendorID on the ProductVendor table in the AdventureWorks2012AdventureWorks2012 database.

DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO  

B.B. 删除多个索引Dropping multiple indexes

以下示例删除 AdventureWorks2012AdventureWorks2012 数据库的单个事务中的两个索引。The following example deletes two indexes in a single transaction in the AdventureWorks2012AdventureWorks2012 database.

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

C.C. 联机删除聚集索引并设置 MAXDOP 选项Dropping a clustered index online and setting the MAXDOP option

以下示例在 ONLINE 选项设置为 ONMAXDOP 设置为 8 的情况下删除聚集索引。The following example deletes a clustered index with the ONLINE option set to ON and MAXDOP set to 8. 因为未指定 MOVE TO 选项,生成的表将与索引存储在相同的文件组中。Because the MOVE TO option was not specified, the resulting table is stored in the same filegroup as the index. 此示例使用 AdventureWorks2012AdventureWorks2012 数据库This examples uses the AdventureWorks2012AdventureWorks2012 database

适用范围:SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017SQL 数据库SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017, SQL 数据库SQL Database.

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

D.D. 联机删除聚集索引并将表移动到新文件组Dropping a clustered index online and moving the table to a new filegroup

以下示例使用 NewGroup 子句联机删除一个聚集索引并将生成表(堆)移动到文件组 MOVE TOThe following example deletes a clustered index online and moves the resulting table (heap) to the filegroup NewGroup by using the MOVE TO clause. 在移动之前和之后,将查询 sys.indexessys.tablessys.filegroups 目录视图,以验证索引和表在文件组中的位置。The sys.indexes, sys.tables, and sys.filegroups catalog views are queried to verify the index and table placement in the filegroups before and after the move. (从 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 开始,可使用 DROP INDEX IF EXISTS 语法。)(Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) you can use the DROP INDEX IF EXISTS syntax.)

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

--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 AdventureWorks2012  
        ADD FILEGROUP NewGroup;  
    ALTER DATABASE AdventureWorks2012  
        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.E. 联机删除 PRIMARY KEY 约束Dropping a PRIMARY KEY constraint online

在创建 PRIMARY KEY 或 UNIQUE 约束时创建的索引不能使用 DROP INDEX 来删除。Indexes that are created as the result of creating PRIMARY KEY or UNIQUE constraints cannot be dropped by using DROP INDEX. 可以使用 ALTER TABLE DROP CONSTRAINT 语句将其删除。They are dropped using the ALTER TABLE DROP CONSTRAINT statement. 有关详细信息,请参阅 ALTER TABLEFor more information, see ALTER TABLE.

以下示例通过删除 PRIMARY KEY 约束删除了具有该约束的聚集索引。The following example deletes a clustered index with a PRIMARY KEY constraint by dropping the constraint. ProductCostHistory 表没有 FOREIGN KEY 约束。The ProductCostHistory table has no FOREIGN KEY constraints. 如果具有此类约束,则必须首先将其删除。If it did, those constraints would have to be removed first.

-- 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.F. 删除 XML 索引Dropping an XML index

以下示例将删除 AdventureWorks2012AdventureWorks2012 数据库中 ProductModel 表上的 XML 索引。The following example drops an XML index on the ProductModel table in the AdventureWorks2012AdventureWorks2012 database.

DROP INDEX PXML_ProductModel_CatalogDescription   
    ON Production.ProductModel;  

G.G. 删除 FILESTREAM 表中的聚集索引Dropping a clustered index on a FILESTREAM table

以下示例使用 MyPartitionScheme 子句和 MOVE TO 子句联机删除聚集索引并将生成的表(堆)和 FILESTREAM 数据移至 FILESTREAM ON 分区方案。The following example deletes a clustered index online and moves the resulting table (heap) and FILESTREAM data to the MyPartitionScheme partition scheme by using both the MOVE TO clause and the FILESTREAM ON clause.

适用范围SQL Server 2008SQL Server 2008SQL Server 2017SQL Server 2017Applies to: SQL Server 2008SQL Server 2008 through SQL Server 2017SQL Server 2017.

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

另请参阅See Also

ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL) ALTER PARTITION SCHEME (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.tables (Transact-SQL) sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL) sys.filegroups (Transact-SQL)
sp_spaceused (Transact-SQL)sp_spaceused (Transact-SQL)