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

  重要提示
  空间索引或 XML 索引不允许使用 MAXDOP。

  max_degree_of_parallelism 可以是：

  • 1
    取消生成并行计划。

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

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

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

  注意
  并行索引操作仅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

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

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

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

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

  注意
  联机索引操作仅在 SQL Server Enterprise Edition、Developer Edition 和 Evaluation Edition 中可用。

• 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

XML 索引

空间索引

权限

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

示例

A. 删除索引

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

B. 删除多个索引

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate 
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO

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

USE AdventureWorks2008R2;
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 AdventureWorks2008R2
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2008R2
        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 约束

USE AdventureWorks2008R2;
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 索引

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

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

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    MOVE TO MyPartitionScheme
    FILESTREAM_ON MyPartitionScheme;
GO

请参阅

参考

概念

其他资源