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 組態選項。如需詳細資訊，請參閱＜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 型樹狀目錄)。當卸除叢集索引時，會從中繼資料移除索引定義，且會將叢集索引分葉層級所儲存的資料列儲存在未排序的結果資料表 (堆積) 中。索引先前所佔用的所有空間都會重新取得。之後，任何資料庫物件都可以使用這個空間。

如果索引所在的檔案群組離線或設為唯讀，便無法卸除索引。

當卸除索引檢視的叢集索引時，會自動卸除相同檢視的所有非叢集索引和自動建立的統計資料。不會卸除手動建立的統計資料。

在 SQL Server 2005 中，index_name ON { table_or_view_name } 是新的語法。維護 table_or_view_name**.**index_name 語法的目的，是為了與舊版相容。在單一交易中組合這兩個選項，陳述式會失敗。您無法利用與舊版相容的語法來卸除 XML 索引。

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

有時候，會卸除再重新建立索引來重新組織或重建索引，例如套用新的填滿因數值，或在大量載入之後重新組織資料。若要做到這一點，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。若要檢視資料表名稱，請在 object_id 上，聯結 sys.indexes 和 sys.tables。如需範例查詢，請參閱 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

請參閱