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 組態選項。如需詳細資訊，請參閱＜max degree of parallelism 選項＞。請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。最大值是 64 個處理器。

  重要事項
  空間索引或 XML 索引不允許 MAXDOP。

  max_degree_of_parallelism 有下列幾種：

  • 1
    抑制產生平行計畫。

  • >1
    將平行索引作業所用的最大處理器數目限制為指定的數目。

  • 0 (預設值)
    根據目前的系統工作負載來使用實際數目的處理器或比實際數目更少的處理器。

  如需詳細資訊，請參閱＜設定平行索引作業＞。

  [!附註]

  只有 SQL Server Enterprise、Developer 和 Evaluation 版本才可使用平行索引作業。

• ONLINE = ON | OFF
  指定在索引作業期間，查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。預設值是 OFF。

  • ON
    不保留長期資料表鎖定。這使得基礎資料表的查詢或更新能夠繼續運作。

  • OFF
    在索引作業期間，會套用資料表鎖定，無法使用資料表。

  只有在卸除叢集索引時，才能指定 ONLINE 選項。如需詳細資訊，請參閱＜備註＞一節。

  [!附註]

  只有 SQL Server Enterprise、Developer 和 Evaluation 版本上才可使用線上索引作業。

• 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 個 (含) 以上之範圍的索引時，Database Engine 會延遲取消配置實際的頁面及其相關聯的鎖定，直到認可交易之後。如需詳細資訊，請參閱＜卸除和重建大型物件＞。

有時候，會卸除再重新建立索引來重新組織或重建索引，例如套用新的填滿因數值，或在大量載入之後重新組織資料。若要做到這一點，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 選項

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

請參閱

參考

概念

其他資源