DROP INDEX (Transact-SQL)

项目
12/15/2008

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

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

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

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

语法

DROP INDEX
{ <drop_relational_or_xml_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

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

参数

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 选项。使用 MAXDOP 可以限制在执行并行计划的过程中使用的处理器数量。最大数量为 64 个处理器。

max_degree_of_parallelism 可以是：
- 1
  取消生成并行计划。
- >1
  将并行索引操作中使用的最大处理器数量限制为指定数量。
- 0（默认值）
  根据当前系统工作负荷使用实际的处理器数量或更少数量的处理器。
有关详细信息，请参阅配置并行索引操作。

注意：

并行索引操作只适用于 SQL Server 2005 Enterprise Edition。

注意：
并行索引操作只适用于 SQL Server 2005 Enterprise Edition。

ONLINE = ON | OFF
指定在索引操作期间基础表和关联的索引是否可用于查询和数据修改操作。默认值为 OFF。
- ON
  不保留长期表锁。这样便允许继续对基础表进行查询或更新。
- OFF
  应用表锁，该表在索引操作期间不可用。
只能在删除聚集索引时指定 ONLINE 选项。有关详细信息，请参阅“备注”部分。

注意：

联机索引操作只适用于 SQL Server 2005 Enterprise Edition。

注意：
联机索引操作只适用于 SQL Server 2005 Enterprise Edition。

MOVE TO
指定一个位置，以移动当前处于聚集索引叶级别的数据行。数据将以堆的形式移动到这一新位置。可以将分区方案或文件组指定为新位置，但该分区方案或文件组必须已存在。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)。

备注

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

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

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

语法 index_name ON { table_or_view_name } 的数据类型为 SQL Server 2005 中新添加的语法。保留了语法table_or_view_name**.**index_name，以便向后兼容。在一个事务中组合使用这两个选项将导致语句失败。使用向后兼容语法无法删除 XML 索引。

删除带有 128 个或更多区数的索引时，SQL Server 2005 数据库引擎将延迟实际页释放及其关联的锁，直到提交事务为止。有关详细信息，请参阅删除并重新生成大型对象。

有时，删除并重新创建索引以重新组织或重新生成索引，例如在大容量加载之后应用新的填充因子值或重新组织数据。若要执行该操作，使用 ALTER INDEX 更为有效，尤其是对于聚集索引而言。ALTER INDEX REBUILD 具有优化功能，可避免重新生成非聚集索引所造成的开销。

使用带选项的 DROP INDEX

在 SQL Server 2005 中，删除聚集索引时可以设置以下索引选项：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 语句的处理器数。有关详细信息，请参阅配置并行索引操作。

XML 索引

删除 XML 索引时，不能指定选项。删除主 XML 索引时，将自动删除所有关联的辅助 XML 索引。有关详细信息，请参阅xml 数据类型列的索引。

权限

若要执行 DROP INDEX，至少需要对表或视图拥有 ALTER 权限。默认情况下，此权限授予 sysadmin 固定服务器角色以及 db_ddladmin 和 db_owner 固定数据库角色。

示例

A. 删除索引

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

USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

B. 删除多个索引

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

USE AdventureWorks;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_VendorAddress_AddressID ON Purchasing.VendorAddress;
GO

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

以下示例在 ONLINE 选项设置为 ON 和 MAXDOP 设置为 8 的情况下删除聚集索引。因为未指定 MOVE TO 选项，生成的表将与索引存储在相同的文件组中。

注意：
该示例只能在 SQL Server 2005 Enterprise Edition 中执行。

USE AdventureWorks;
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 AdventureWorks;
GO
--Create a clustered index on the PRIMARY filegroup if it 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.
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the ALTER DATABASE statement 
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks
        ADD FILEGROUP NewGroup;
    EXECUTE ('ALTER DATABASE AdventureWorks
        ADD FILE (NAME = File1,
            FILENAME = '''+ @data_path + '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 AdventureWorks;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.ProductCostHistory
    DROP CONSTRAINT PK_ProductCostHistory_ProductID_StartDate
        WITH (ONLINE = ON);
GO

F. 删除 XML 索引

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

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

请参阅