DROP INDEX (Transact-SQL)

DROP INDEX 陳述式會從目前的 SQL Server 資料庫刪除索引。若要使用 [物件總管] 來刪除索引，請參閱＜如何：刪除索引 (SQL Server Management Studio)＞。

您無法使用 DROP INDEX 陳述式移除包含 PRIMARY KEY 或 UNIQUE 條件約束的索引。若要移除條件約束然後刪除索引，請搭配 DROP CONSTRAINT 子句使用 ALTER TABLE。

若要使用 DROP INDEX 陳述式刪除叢集索引，再將產生的資料表移到另一個檔案群組或資料分割配置，請指定 MOVE TO 選項。

重要事項
在未來的 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 (預設值)
    根據目前的系統工作負載來使用實際數目的處理器或比實際數目更少的處理器。

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

  [!附註]

  只有 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 使用選項

您可以在卸除叢集索引時，設定下列索引選項：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 陳述式的處理器數目。如需詳細資訊，請參閱＜設定平行索引作業＞。

當卸除叢集索引時，除非修改了資料分割配置，否則對應的堆積資料分割會保留其資料壓縮設定。如果資料分割配置有所變更，所有資料分割都會重建為未壓縮的狀態 (DATA_COMPRESSION = NONE)。若要卸除叢集索引及變更資料分割配置，您需要執行以下兩個步驟：

1. 卸除叢集索引。

2. 使用指定壓縮選項的 ALTER TABLE ...REBUILD ... 選項來修改資料表。

如果在離線狀態卸除叢集索引，則只會移除叢集索引的上層；因此，此作業的速度相當快。在離線狀態下卸除叢集索引時，SQL Server 會重建堆積兩次，一次在步驟 1，另一次在步驟 2。如需有關資料壓縮的詳細資訊，請參閱＜建立壓縮資料表及索引＞。

XML 索引

當您卸除 XML 索引時，無法指定選項。此外，您也不能使用 table_or_view_name**.**index_name 語法。當卸除主要 XML 索引時，也會自動卸除所有相關聯的次要 XML 索引。如需詳細資訊，請參閱＜XML 資料類型資料行中的索引＞。

空間索引

只有資料表上才支援空間索引。當您卸除空間索引時，不能指定任何選項或使用 **.**index_name。正確的語法如下：

DROP INDEX spatial_index_name ON spatial_table_name;

如需有關空間索引的詳細資訊，請參閱＜使用空間索引 (Database Engine)＞。

權限

若要執行 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

G. 卸除 FILESTREAM 資料表上的叢集索引

下列範例會在線上刪除叢集索引，並將產生的資料表 (堆積) 和 FILESTREAM 資料移到 MyPartitionScheme 資料分割配置，其方式是同時使用 MOVE TO 子句和 FILESTREAM ON 子句。

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    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)

概念

卸除索引

其他資源

決定索引的磁碟空間需求