DROP INDEX (Transact-SQL)

文章
05/17/2024

適用於： SQL Server Azure SQL 資料庫 Azure SQL 受控執行個體 Azure Synapse Analytics Analytics Platform System (PDW)

從目前資料庫中移除一或多個關聯式索引、空間索引、已篩選的索引或 XML 索引。您可以藉由指定 MOVE TO 選項，卸除叢集索引，並將產生的數據表移至單一交易中的另一個檔案群組或分割區配置。

語句DROP INDEX不適用於定義或 UNIQUE 條件約束所建立的PRIMARY KEY索引。若要移除條件約束和對應的索引，請使用 ALTER TABLE 搭配 DROP CONSTRAINT 子句。

重要

中 <drop_backward_compatible_index> 定義的語法將會在未來的 SQL Server 版本中移除。請避免在新的開發工作中使用這個語法，並規劃修改目前在使用這個語法的應用程式。請改用 <drop_relational_or_xml_or_spatial_index> 下所指定的語法。 XML 索引無法使用回溯相容語法卸除。

Transact-SQL 語法慣例

Syntax

SQL Server 的語法（檔案群組和 filestream 以外的所有選項都適用於 Azure SQL 資料庫）。

DROP INDEX [ IF EXISTS ]
{ <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.table_or_view_name | schema_name.table_or_view_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" } ]
}

Azure SQL 資料庫語法。

DROP INDEX
{ <drop_relational_or_xml_or_spatial_index> [ , ...n ]
}

<drop_relational_or_xml_or_spatial_index> ::=
    index_name ON <object>

<object> ::=
{ database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }

Azure Synapse Analytics 和分析平台系統（PDW）的語法。

DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

引數

IF EXISTS

適用於：SQL Server 2016 (13.x) 和更新版本。

只有在索引已存在時，才能有條件地將其卸除。

index_name

要卸除之索引的名稱。

database_name

資料庫的名稱。

schema_name

資料表或檢視所屬之結構描述的名稱。

table_or_view_name

這是與索引相關聯的資料表或檢視表的名稱。只有資料表上才支援空間索引。

若要顯示物件的索引報表，請使用 sys.indexes 目錄檢視。

Azure SQL 資料庫支援三部分名稱格式：database_name.[schema_name].object_name當 database_name 是目前資料庫，或 database_name 為 tempdb ，且object_name開頭為 #時。

<drop_clustered_index_option>

適用於：SQL Server 2008 （10.0.x）和更新版本，SQL 資料庫。

控制叢集索引選項。這些選項無法與其他索引類型搭配使用。

MAXDOP = max_degree_of_parallelism

適用於：SQL Server 2008 （10.0.x）和更新版本，SQL 資料庫（僅限效能等級 P2 和 P3）。

在索引作業期間，覆寫 max degree of parallelism 組態選項。如需詳細資訊，請參閱設定平行處理原則的最大程度（伺服器組態選項）。使用 MAXDOP 來限制平行計劃執行中使用的處理器數目。最大值是 64 個處理器。

重要

MAXDOP 空間索引或 XML 索引不允許使用。

max_degree_of_parallelism可以是下列其中一個值。

值	Description
`1`	隱藏平行計劃產生
`>1`	將平行索引作業中使用的處理器數目上限限制為指定的數位
`0` (預設值)	根據目前的系統工作負載，使用實際處理器數目或更少數目

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

注意

SQL Server 的所有版本都無法使用平行索引作業。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

ONLINE = ON | OFF

適用於：SQL Server 2008 （10.0.x）和更新版本 Azure SQL 資料庫。

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

ON：不會保留長期數據表鎖定。這使得基礎資料表的查詢或更新能夠繼續運作。
OFF：套用數據表鎖定，而且數據表在索引作業期間無法使用。

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

注意

SQL Server 的每個版本都無法使用在線索引作業。如需 SQL Server 版本支援的功能清單，請參閱 SQL Server 2022 的版本和支援功能。

移至 { partition_scheme_name （ column_name ） | filegroup_name |“default” }

適用於：SQL Server 2008 (10.0.x) 和更新版本。 SQL 資料庫支援"default"作為檔案組名。

指定目前在叢集索引分葉層級之資料列所要移往的位置。資料會以堆積的形式移至新位置。您可以指定資料分割配置或檔案群組來作為新位置，但是這個資料分割配置或檔案群組必須已經存在。 MOVE TO 對索引檢視或非叢集索引無效。如果未指定數據分割配置或檔案群組，產生的數據表會位於與叢集索引定義的相同數據分割配置或檔案群組中。

如果使用卸除叢集索引 MOVE TO，則會重建基表上任何非叢集索引，但會保留在原始檔案群組或分割區配置中。如果基表移至不同的檔案群組或分割區配置，則不會移動非叢集索引以配合基表的新位置（堆積）。因此，即使非叢集索引先前與叢集索引對齊，它們也可能不再與堆積對齊。如需數據分割索引對齊的詳細資訊，請參閱分割數據表和索引。

partition_scheme_name ( column_name )

適用於：SQL Server 2008 （10.0.x）和更新版本，SQL 資料庫。

指定一個資料分割結構描述來做為結果資料表的位置。必須藉由執行 CREATE PARTITION SCHEME 或 ALTER PARTITION SCHEME，來建立數據分割配置。如果未指定位置，且資料表已進行資料分割，便會將資料表併入與現有叢集索引相同的資料分割配置中。

配置中的數據行名稱不限於索引定義中的數據行。您可以指定基底資料表中的任何資料行。

filegroup_name

適用於：SQL Server 2008 (10.0.x) 和更新版本。

指定一個檔案群組來做為結果資料表的位置。如果未指定任何位置且數據表未分割，則產生的數據表會包含在與叢集索引相同的檔案群組中。此檔案群組必須已存在。

"default"

指定產生資料表的預設位置。

注意

在此內容中，default 不是關鍵字。它是預設檔案群組的識別碼，必須加以分隔，例如 MOVE TO "default" 或 MOVE TO [default]。如果 "default" 已指定， QUOTED_IDENTIFIER 則必須為目前的會話設定 ON 選項。這是預設值。如需詳細資訊，請參閱 SET QUOTED_IDENTIFIER。

FILESTREAM_ON { partition_scheme_name filestream_filegroup_name | |“default” }

適用於：SQL Server 2008 (10.0.x) 和更新版本。

指定目前在叢集索引分葉層級之 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。

備註

卸除非叢集索引時，索引定義會從元數據中移除，而索引數據頁（B 型樹狀結構）則會從資料庫檔案中移除。當卸除叢集索引時，會從中繼資料移除索引定義，且會將叢集索引分葉層級所儲存的資料列儲存在未排序的結果資料表 (堆積) 中。索引先前所佔用的所有空間都會重新取得。之後，任何資料庫物件都可以使用這個空間。

注意

檔使用一般參考索引的 B 型樹狀結構一詞。在數據列存放區索引中，資料庫引擎會實作 B+ 樹狀結構。這不適用於記憶體優化數據表上的數據行存放區索引或索引。如需詳細資訊，請參閱 SQL Server 和 Azure SQL 索引架構和設計指南。

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

卸除索引檢視表的叢集索引時，會自動卸除相同檢視表上所有非叢集索引和自動建立的統計數據。不會卸除手動建立的統計數據。

<table_or_view_name>.<index_name>語法會維持為回溯相容性。 XML 索引或空間索引無法使用回溯相容語法來卸除。

當卸除含有 128 個 (含) 以上之範圍的索引時，資料庫引擎會延遲實際的頁面解除配置及其相關聯的鎖定，直到認可交易之後。

有時候，會卸除再重新建立索引來重新組織或重建索引，例如套用新的填滿因數值，或在大量載入之後重新組織資料。若要這樣做，使用 ALTER INDEX 會更有效率，特別是針對叢集索引。 ALTER INDEX REBUILD 有優化以防止重建非叢集索引的額外負荷。

搭配DROP INDEX 使用選項

當您卸除叢集索引時，可以設定下列索引選項： MAXDOP、 ONLINE和 MOVE TO。

使用 MOVE TO 卸除叢集索引，並將產生的數據表移至單一交易中的另一個檔案群組或分割區配置。

當您指定 ONLINE = ON時，交易不會封鎖 DROP INDEX 對基礎數據和相關聯非叢集索引的查詢和修改。您只能每次在線上卸除一個叢集索引。如需選項的完整描述 ONLINE ，請參閱 CREATE INDEX。

如果檢視上停用索引，或包含 text、ntext、image、varchar（max）、nvarchar（max）、varbinary（max）或分葉層級數據列中的 xml 數據行，則您無法在在線卸除叢集索引。

ONLINE = ON使用和 MOVE TO 選項需要更多暫存磁碟空間。

卸除索引之後，產生的堆積會出現在數據行的 sys.indexes 目錄檢視 NULL 中 name 。若要檢視數據表名稱，請在上object_id聯結 sys.indexes 。sys.tables 如需範例查詢，請參閱 D 範例。

在執行 SQL Server 2005 Enterprise Edition 或更新版本的多處理器電腦上， DROP INDEX 可能會使用更多處理器來執行與卸除叢集索引相關聯的掃描和排序作業，就像其他查詢一樣。您可以指定MAXDOP索引選項，手動設定用來執行語句的DROP INDEX處理器數目。如需詳細資訊，請參閱設定平行索引作業。

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

卸除叢集索引。
使用 ALTER TABLE ... REBUILD ... 指定壓縮選項的選項修改數據表。

卸除 OFFLINE叢集索引時，只會移除叢集索引的上層，因此作業速度很快。卸除 ONLINE叢集索引時，SQL Server 會重建堆積兩次，一次用於步驟 1，一次用於步驟 2。如需數據壓縮的詳細資訊，請參閱數據壓縮。

XML 索引

當您卸除 XML 索引時，無法指定選項。此外，您無法使用 <table_or_view_name>.<index_name> 語法。當卸除主要 XML 索引時，也會自動卸除所有相關聯的次要 XML 索引。如需詳細資訊，請參閱 XML 索引 (SQL Server)。

空間索引

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

DROP INDEX <spatial_index_name> ON <spatial_table_name>;

如需空間索引的詳細資訊，請參閱空間索引概觀。

權限

若要執行 DROP INDEX，至少需要資料表或檢視表的 ALTER 權限。依預設，這個權限會授與 系統管理員 固定伺服器角色以及 db_ddladmin 和 db_owner 固定資料庫角色。

範例

本文 Transact-SQL 程式碼範例使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，從 Microsoft SQL Server Samples 和 Community Projects （Microsoft SQL Server 範例和社群專案）首頁即可下載。

A. 卸除索引

下列範例會刪除 AdventureWorks2022 資料庫中數據表上的索引IX_ProductVendor_BusinessEntityIDProductVendor。

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. 卸除多個索引

下列範例會在 AdventureWorks2022 資料庫中的單一交易中刪除兩個索引。

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 2008 （10.0.x）和更新版本，SQL 資料庫。

DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);
GO

D. 在線卸除叢集索引，並將數據表移至新的檔案群組

下列範例會在線上刪除叢集索引，並利用 NewGroup 子句，將產生的資料表 (堆積) 移到 MOVE TO 檔案群組。它會查詢 sys.indexes、 sys.tables和 sys.filegroups 目錄檢視來確認在移動之前和之後，索引和資料表在檔案群組中的位置。從 SQL Server 2016 （13.x）開始，您可以使用 DROP INDEX IF EXISTS 語法。

適用於：SQL Server 2008 (10.0.x) 和更新版本。

--Create a clustered index on the PRIMARY filegroup if the index does not exist.
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 AdventureWorks2022
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2022
        ADD FILE (NAME = File1,
            FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.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 條件約束

因為DROP INDEX建立PRIMARY KEY或UNIQUE條件約束而建立的索引無法使用卸除。它們會使用 ALTER TABLE DROP CONSTRAINT 語句卸除。如需詳細資訊，請參閱 ALTER TABLE。

下列範例會藉由卸除條件約束來刪除具有 PRIMARY KEY 條件約束的叢集索引。數據表 ProductCostHistory 沒有 FOREIGN KEY 條件約束。如果有的話，您必須先移除這些條件約束。

-- 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);

F. 卸除 XML 索引

下列範例會卸除 AdventureWorks2022 資料庫中數據表的 XML 索引 ProductModel 。

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. 卸除 FILESTREAM 數據表上的叢集索引

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

適用於：SQL Server 2008 (10.0.x) 和更新版本。

DROP INDEX PK_MyClusteredIndex
    ON dbo.MyTable
    WITH (MOVE TO MyPartitionScheme,
          FILESTREAM_ON MyPartitionScheme);
GO

分享方式：

DROP INDEX (Transact-SQL)

Syntax

引數

IF EXISTS

index_name

database_name

schema_name

table_or_view_name

<drop_clustered_index_option>

MAXDOP = max_degree_of_parallelism

ONLINE = ON | OFF

移至 { partition_scheme_name （ column_name ） | filegroup_name |“default” }

partition_scheme_name ( column_name )

filegroup_name

"default"

FILESTREAM_ON { partition_scheme_name filestream_filegroup_name | |“default” }

partition_scheme_name

filestream_filegroup_name

"default"

備註

搭配DROP INDEX 使用選項

XML 索引

空間索引

權限

範例

A. 卸除索引

B. 卸除多個索引

C. 在在線卸除叢集索引並設定 MAXDOP 選項

D. 在線卸除叢集索引，並將數據表移至新的檔案群組

E. 在線卸除 PRIMARY KEY 條件約束

F. 卸除 XML 索引

G. 卸除 FILESTREAM 數據表上的叢集索引

意見反映

更多資源

分享方式：

DROP INDEX (Transact-SQL)

Syntax

引數

IF EXISTS

index_name

database_name

schema_name

table_or_view_name

<drop_clustered_index_option>

MAXDOP = max_degree_of_parallelism

ONLINE = ON | OFF

移至 { partition_scheme_name （ column_name ） | filegroup_name |“default” }

partition_scheme_name ( column_name )

filegroup_name

"default"

FILESTREAM_ON { partition_scheme_name filestream_filegroup_name | |“default” }

partition_scheme_name

filestream_filegroup_name

"default"

備註

搭配DROP INDEX 使用選項

XML 索引

空間索引

權限

範例

A. 卸除索引

B. 卸除多個索引

C. 在在線卸除叢集索引並設定 MAXDOP 選項

D. 在線卸除叢集索引，並將數據表移至新的檔案群組

E. 在線卸除 PRIMARY KEY 條件約束

F. 卸除 XML 索引

G. 卸除 FILESTREAM 數據表上的叢集索引

相關內容

意見反映

更多資源