DROP INDEX (Transact-SQL)

  • 项目

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

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

重要说明重要提示

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

主题链接图标 Transact-SQL 语法约定

语法

DROP INDEX
{ <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 ] . | 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" 
            }
  [ FILESTREAM_ON { partition_scheme_name 
            | filestream_filegroup_name 
            | "default" } ]
}

参数

  • index_name
    要删除的索引名称。

  • database_name
    数据库的名称。

  • schema_name
    表或视图所属架构的名称。

  • table_or_view_name
    与该索引关联的表或视图的名称。 只有表支持空间索引。

    若要显示对象的索引报表，请使用 sys.indexes 目录视图。

  • <drop_clustered_index_option>
    控制聚集索引选项。 这些选项不能与其他索引类型一起使用。

  • MAXDOP = max_degree_of_parallelism
    在索引操作期间覆盖 max degree of parallelism 配置选项。 有关详细信息，请参阅配置 max degree of parallelism 服务器配置选项。 使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。 最大数量为 64 个处理器。

    重要说明重要提示

    空间索引或 XML 索引不允许使用 MAXDOP。

    max_degree_of_parallelism 可以是：

    • 1
      取消生成并行计划。

    • >1
      将并行索引操作使用的最大处理器数量限制为指定的数量。

    • 0（默认值）
      根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。

    有关详细信息，请参阅配置并行索引操作。

    注意注意

    并非 Microsoft SQL Server 的每个版本均支持并行索引操作。 有关 SQL Server 的每个版本支持的功能列表，请参阅 SQL Server 2012 各个版本支持的功能。

  • ONLINE = ON | OFF
    指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。 默认值为 OFF。

    • ON
      不保留长期表锁。 这样便允许继续对基础表进行查询或更新。

    • OFF
      应用表锁，该表在索引操作期间不可用。

    只能在删除聚集索引时指定 ONLINE 选项。 有关详细信息，请参见“备注”部分。

    注意注意

    在 Microsoft SQL Server 的每个版本中均不支持联机索引操作。 有关 SQL Server 的每个版本支持的功能列表，请参阅 SQL Server 2012 各个版本支持的功能。

  • MOVE TO { partition_scheme_name**(column_name)** | filegroup_name | "default"
    指定一个位置，以移动当前处于聚集索引叶级别的数据行。 数据将以堆的形式移动到这一新位置。 可以将分区方案或文件组指定为新位置，但该分区方案或文件组必须已存在。 MOVE TO 对索引视图或非聚集索引无效。 如果未指定分区方案或文件组，则生成的表将位于为聚集索引定义的同一分区方案或文件组中。

    如果使用 MOVE TO 删除了聚集索引，则将重新生成所有对基表的非聚集索引，但这些索引会保留在其原始文件组或分区方案中。 如果基表移动到其他文件组或分区方案中，这些非聚集索引不会通过移动来与基表（堆）的新位置一致。 因此，即使非聚集索引以前与聚集索引对齐，它们也可能不再与堆对齐。 有关对齐已分区索引的详细信息，请参阅已分区表和已分区索引。

    • partition_scheme_name ( column_name )
      指定分区方案作为生成表的位置。 该分区方案必须是已通过 CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME 创建好的。 如果未指定位置但表已分区，则表将包含在与现有聚集索引相同的分区方案中。

      方案中的列名不限制为索引定义中的列。 可以指定基表中的任何列。

    • filegroup_name
      指定文件组作为生成表的位置。 如果未指定位置并且表是未分区的，则生成的表将包含在与聚集索引相同的文件组中。 该文件组必须已存在。

    • "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" }
    指定一个位置，当前处于聚集索引叶级别的 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)。

注释

删除非聚集索引时，将从元数据中删除索引定义，并从数据库文件中删除索引数据页（B 树）。 删除聚集索引时，将从元数据中删除索引定义，并且存储于聚集索引叶级别的数据行将存储到生成的未排序表（堆）中。 将重新获得以前由索引占有的所有空间。 此后可将该空间用于任何数据库对象。

如果索引所在的文件组脱机或设置为只读，则不能删除该索引。

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

保留了语法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 目录视图中，且 name 列中为 NULL。 若要查看表名，请将 sys.indexes 和 sys.tables 联接在 object_id 上。 有关示例查询的信息，请参阅示例 D。

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

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

  1. 删除聚集索引。

  2. 通过使用指定压缩选项的 ALTER TABLE ... REBUILD ... 选项来修改表。

脱机删除聚集索引时，只删除较高级别的聚集索引；因此，该操作的执行速度很快。 如果联机删除聚集索引，则 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 固定数据库角色。

示例

A.删除索引

以下示例删除 ProductVendor 表上的索引 IX_ProductVendor_VendorID。

USE AdventureWorks2012;
GO
DROP INDEX IX_ProductVendor_BusinessEntityID 
    ON Purchasing.ProductVendor;
GO

B.删除多个索引

以下示例删除单个事务中的两个索引。

USE AdventureWorks2012;
GO
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 2005 Enterprise Edition 或更高版本中执行。

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

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

以下示例使用 MOVE TO 子句联机删除一个聚集索引并将生成表（堆）移动到文件组 NewGroup。 在移动之前和之后，将查询 sys.indexes、 sys.tables 和 sys.filegroups 目录视图，以验证索引和表在文件组中的位置。

USE AdventureWorks2012;
GO
--Create a clustered index on the PRIMARY filegroup if the index does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
            N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
    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\MSSQL10.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 约束

在创建 PRIMARY KEY 或 UNIQUE 约束时创建的索引不能使用 DROP INDEX 来删除。 可以使用 ALTER TABLE DROP CONSTRAINT 语句将其删除。 有关详细信息，请参阅 ALTER TABLE。

以下示例通过删除 PRIMARY KEY 约束删除了具有该约束的聚集索引。 ProductCostHistory 表没有 FOREIGN KEY 约束。 如果具有此类约束，则必须首先将其删除。

USE AdventureWorks2012;
GO
-- 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);

GO

F.删除 XML 索引

以下示例删除 ProductModel 表的 XML 索引。

USE AdventureWorks2012;
GO
DROP INDEX PXML_ProductModel_CatalogDescription 
    ON Production.ProductModel;
GO

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

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

USE MyDatabase;
GO
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)