DROP INDEX (Transact-SQL)DROP INDEX (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

從目前資料庫中移除一或多個關聯式索引、空間索引、已篩選的索引或 XML 索引。Removes one or more relational, spatial, filtered, or XML indexes from the current database. 您可以卸除叢集索引,再藉由指定 MOVE TO 選項,於單一交易中將結果資料表移到另一個檔案群組或資料分割配置。You can drop a clustered index and move the resulting table to another filegroup or partition scheme in a single transaction by specifying the MOVE TO option.

DROP INDEX 陳述式不會套用在定義 PRIMARY KEY 或 UNIQUE 條件約束所建立的索引上。The DROP INDEX statement does not apply to indexes created by defining PRIMARY KEY or UNIQUE constraints. 若要移除條件約束和對應的索引,請搭配 DROP CONSTRAINT 子句來使用 ALTER TABLETo remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause.

重要

在未來的 MicrosoftMicrosoftSQL ServerSQL Server 版本中,將移除 <drop_backward_compatible_index> 所定義的語法。The syntax defined in <drop_backward_compatible_index> will be removed in a future version of MicrosoftMicrosoftSQL ServerSQL Server. 請避免在新的開發工作中使用這個語法,並規劃修改目前在使用這個語法的應用程式。Avoid using this syntax in new development work, and plan to modify applications that currently use the feature. 請改用 <drop_relational_or_xml_index> 下所指定的語法。Use the syntax specified under <drop_relational_or_xml_index> instead. 您無法利用與舊版相容的語法來卸除 XML 索引。XML indexes cannot be dropped using backward compatible syntax.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

-- Syntax for SQL Server (All options except filegroup and filestream apply to Azure SQL Database.)  
  
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" } ]  
}  
-- Syntax for Azure SQL Database  
  
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 }  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

引數Arguments

IF EXISTSIF EXISTS
適用於SQL ServerSQL Server (SQL Server 2016 (13.x)SQL Server 2016 (13.x)目前版本)。Applies to: SQL ServerSQL Server ( SQL Server 2016 (13.x)SQL Server 2016 (13.x) through current version).

只有在索引已存在時,才能有條件地將其卸除。Conditionally drops the index only if it already exists.

index_nameindex_name
這是要卸除的索引名稱。Is the name of the index to be dropped.

database_namedatabase_name
這是資料庫的名稱。Is the name of the database.

schema_nameschema_name
這是資料表或檢視表所屬的結構描述名稱。Is the name of the schema to which the table or view belongs.

table_or_view_nametable_or_view_name
這是與索引相關聯的資料表或檢視表的名稱。Is the name of the table or view associated with the index. 只有資料表上才支援空間索引。Spatial indexes are supported only on tables.

若要顯示物件的索引報表,請使用 sys.indexes 目錄檢視。To display a report of the indexes on an object, use the sys.indexes catalog view.

當 database_name 是目前的資料庫或 database_name 是 tempdb,而且 object_name 開頭為 # 時,Azure SQL Database 支援三部分名稱格式 database_name.[schema_name].object_name。Azure SQL Database supports the three-part name format database_name.[schema_name].object_name when the database_name is the current database or the database_name is tempdb and the object_name starts with #.

<drop_clustered_index_option><drop_clustered_index_option>
適用於SQL Server 2008SQL Server 2008 及更新版本、SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 and later, SQL DatabaseSQL Database.

控制叢集索引選項。Controls clustered index options. 這些選項無法搭配其他索引類型使用。These options cannot be used with other index types.

MAXDOP = max_degree_of_parallelismMAXDOP = max_degree_of_parallelism
適用於SQL Server 2008SQL Server 2008 及更新版本、SQL DatabaseSQL Database (僅限效能等級 P2 和 P3)。Applies to: SQL Server 2008SQL Server 2008 and later, SQL DatabaseSQL Database (Performance Levels P2 and P3 only).

在索引作業期間,覆寫 max degree of parallelism 設定選項。Overrides the max degree of parallelism configuration option for the duration of the index operation. 如需詳細資訊,請參閱 設定 max degree of parallelism 伺服器組態選項For more information, see Configure the max degree of parallelism Server Configuration Option. 請利用 MAXDOP 來限制執行平行計畫所用的處理器數目。Use MAXDOP to limit the number of processors used in a parallel plan execution. 最大值是 64 個處理器。The maximum is 64 processors.

重要

空間索引或 XML 索引不允許 MAXDOP。MAXDOP is not allowed for spatial indexes or XML indexes.

max_degree_of_parallelism 可以是:max_degree_of_parallelism can be:

11
隱藏平行計畫的產生。Suppresses parallel plan generation.

>1>1
將平行索引作業所用的最大處理器數目限制為指定的數目。Restricts the maximum number of processors used in a parallel index operation to the specified number.

0 (預設值)0 (default)
根據目前的系統工作負載,使用實際數目或比實際數目更少的處理器。Uses the actual number of processors or fewer based on the current system workload.

如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

注意

SQL ServerSQL Server 的所有版本都無法使用平行索引作業。Parallel index operations are not available in every edition of SQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

ONLINE = ON | OFFONLINE = ON | OFF
適用於SQL Server 2008SQL Server 2008 及更新版本、Azure SQL DatabaseAzure SQL DatabaseApplies to: SQL Server 2008SQL Server 2008 and later, Azure SQL DatabaseAzure SQL Database.

指定在索引作業期間,查詢和資料修改是否能夠使用基礎資料表和相關聯的索引。Specifies whether underlying tables and associated indexes are available for queries and data modification during the index operation. 預設值為 OFF。The default is OFF.

開啟ON
不保留長期資料表鎖定。Long-term table locks are not held. 這使得基礎資料表的查詢或更新能夠繼續運作。This allows queries or updates to the underlying table to continue.

OFFOFF
在索引作業期間,會套用資料表鎖定,無法使用資料表。Table locks are applied and the table is unavailable for the duration of the index operation.

只有在卸除叢集索引時,才能指定 ONLINE 選項。The ONLINE option can only be specified when you drop clustered indexes. 如需詳細資訊,請參閱<備註>一節。For more information, see the Remarks section.

注意

SQL ServerSQL Server的所有版本都無法使用線上索引作業。Online index operations are not available in every edition of SQL ServerSQL Server. 如需 SQL ServerSQL Server 版本支援的功能清單,請參閱 SQL Server 2016 版本和支援的功能For a list of features that are supported by the editions of SQL ServerSQL Server, see Editions and Supported Features for SQL Server 2016.

MOVE TO { partition_scheme_name ( column_name ) | filegroup_name | " default "MOVE TO { partition_scheme_name(column_name) | filegroup_name | " default "
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later. SQL DatabaseSQL Database 支援以 "default" 為檔案群組名稱。supports "default" as the filegroup name.

指定目前在叢集索引分葉層級之資料列所要移往的位置。Specifies a location to move the data rows that currently are in the leaf level of the clustered index. 資料會以堆積的形式移至新位置。The data is moved to the new location in the form of a heap. 您可以指定資料分割配置或檔案群組來作為新位置,但是這個資料分割配置或檔案群組必須已經存在。You can specify either a partition scheme or filegroup as the new location, but the partition scheme or filegroup must already exist. MOVE TO 對於索引檢視表或非叢集索引無效。MOVE TO is not valid for indexed views or nonclustered indexes. 如果未指定資料分割結構描述或檔案群組,結果資料表會放在定義給叢集索引的相同資料分割結構描述或檔案群組中。If a partition scheme or filegroup is not specified, the resulting table will be located in the same partition scheme or filegroup as was defined for the clustered index.

如果利用 MOVE TO 卸除叢集索引,便會重建基底資料表的任何非叢集索引,不過,它們會保留在原始檔案群組或資料分割結構描述中。If a clustered index is dropped by using MOVE TO, any nonclustered indexes on the base table are rebuilt, but they remain in their original filegroups or partition schemes. 如果將基底資料表移到不同的檔案群組或資料分割結構描述中,則不會移動非叢集索引來符合基底資料表 (堆積) 的新位置。If the base table is moved to a different filegroup or partition scheme, the nonclustered indexes are not moved to coincide with the new location of the base table (heap). 因此,即使非叢集索引先前與叢集索引對齊,它們也可能不再與堆積對齊。Therefore, even if the nonclustered indexes were previously aligned with the clustered index, they might no longer be aligned with the heap. 如需資料分割索引對齊的詳細資訊,請參閱資料分割資料表與索引For more information about partitioned index alignment, see Partitioned Tables and Indexes.

partition_scheme_name ( column_name )partition_scheme_name ( column_name )
適用於SQL Server 2008SQL Server 2008 及更新版本、SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 and later, SQL DatabaseSQL Database.

指定一個資料分割結構描述來做為結果資料表的位置。Specifies a partition scheme as the location for the resulting table. 您必須已執行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME 來建立資料分割結構描述。The partition scheme must have already been created by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. 如果未指定位置,且資料表已進行資料分割,便會將資料表併入與現有叢集索引相同的資料分割配置中。If no location is specified and the table is partitioned, the table is included in the same partition scheme as the existing clustered index.

配置中的資料行名稱不限定為索引定義中的資料行。The column name in the scheme is not restricted to the columns in the index definition. 您可以指定基底資料表中的任何資料行。Any column in the base table can be specified.

filegroup_namefilegroup_name
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定一個檔案群組來做為結果資料表的位置。Specifies a filegroup as the location for the resulting table. 如果未指定位置,且資料表未進行資料分割,便會將結果資料表包括在叢集索引的相同檔案群組中。If no location is specified and the table is not partitioned, the resulting table is included in the same filegroup as the clustered index. 此檔案群組必須已存在。The filegroup must already exist.

" default "" default "
指定產生資料表的預設位置。Specifies the default location for the resulting table.

注意

在此內容中,default 不是關鍵字。In this context, default is not a keyword. 它是預設檔案群組的識別碼,必須加以分隔,如 MOVE TO " default " 或 MOVE TO [ default ]It is an identifier for the default filegroup and must be delimited, as in MOVE TO " default " or MOVE TO [ default ]. 如果指定了 " default " ,則目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。If " default " is specified, the QUOTED_IDENTIFIER option must be set ON for the current session. 這是預設值。This is the default setting. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | " default " }FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | " default " }
適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

指定目前在叢集索引分葉層級之 FILESTREAM 資料表所要移往的位置。Specifies a location to move the FILESTREAM table that currently is in the leaf level of the clustered index. 資料會以堆積的形式移至新位置。The data is moved to the new location in the form of a heap. 您可以指定資料分割配置或檔案群組來作為新位置,但是這個資料分割配置或檔案群組必須已經存在。You can specify either a partition scheme or filegroup as the new location, but the partition scheme or filegroup must already exist. FILESTREAM ON 對於索引檢視表或非叢集索引是無效的。FILESTREAM ON is not valid for indexed views or nonclustered indexes. 如果未指定資料分割配置,資料將會放在針對叢集索引所定義的相同資料分割配置中。If a partition scheme is not specified, the data will be located in the same partition scheme as was defined for the clustered index.

partition_scheme_namepartition_scheme_name
為 FILESTREAM 資料指定資料分割配置。Specifies a partition scheme for the FILESTREAM data. 您必須已執行 CREATE PARTITION SCHEMEALTER PARTITION SCHEME 來建立資料分割結構描述。The partition scheme must have already been created by executing either CREATE PARTITION SCHEME or ALTER PARTITION SCHEME. 如果未指定位置,且資料表已進行資料分割,便會將資料表併入與現有叢集索引相同的資料分割配置中。If no location is specified and the table is partitioned, the table is included in the same partition scheme as the existing clustered index.

如果您為 MOVE TO 指定資料分割配置,您必須針對 FILESTREAM ON 使用相同的資料分割配置。If you specify a partition scheme for MOVE TO, you must use the same partition scheme for FILESTREAM ON.

filestream_filegroup_namefilestream_filegroup_name
為 FILESTREAM 資料指定 FILESTREAM 檔案群組。Specifies a FILESTREAM filegroup for FILESTREAM data. 如果未指定位置,且資料表未分割,則資料會併入預設的 FILESTREAM 檔案群組中。If no location is specified and the table is not partitioned, the data is included in the default FILESTREAM filegroup.

" default "" default "
為 FILESTREAM 資料指定預設位置。Specifies the default location for the FILESTREAM data.

注意

在此內容中,default 不是關鍵字。In this context, default is not a keyword. 它是預設檔案群組的識別碼,必須加以分隔,如 MOVE TO " default " 或 MOVE TO [ default ]It is an identifier for the default filegroup and must be delimited, as in MOVE TO " default " or MOVE TO [ default ]. 如果指定了 "default",目前工作階段的 QUOTED_IDENTIFIER 選項就必須是 ON。If "default" is specified, the QUOTED_IDENTIFIER option must be ON for the current session. 這是預設值。This is the default setting. 如需詳細資訊,請參閱 SET QUOTED_IDENTIFIER (Transact-SQL)For more information, see SET QUOTED_IDENTIFIER (Transact-SQL).

備註Remarks

當卸除非叢集索引時,會從中繼資料移除索引定義,從資料庫檔案中移除索引資料頁面 (B 型樹狀目錄)。When a nonclustered index is dropped, the index definition is removed from metadata and the index data pages (the B-tree) are removed from the database files. 當卸除叢集索引時,會從中繼資料移除索引定義,且會將叢集索引分葉層級所儲存的資料列儲存在未排序的結果資料表 (堆積) 中。When a clustered index is dropped, the index definition is removed from metadata and the data rows that were stored in the leaf level of the clustered index are stored in the resulting unordered table, a heap. 索引先前所佔用的所有空間都會重新取得。All the space previously occupied by the index is regained. 之後,任何資料庫物件都可以使用這個空間。This space can then be used for any database object.

如果索引所在的檔案群組離線或設為唯讀,便無法卸除索引。An index cannot be dropped if the filegroup in which it is located is offline or set to read-only.

當卸除索引檢視的叢集索引時,會自動卸除相同檢視的所有非叢集索引和自動建立的統計資料。When the clustered index of an indexed view is dropped, all nonclustered indexes and auto-created statistics on the same view are automatically dropped. 不會卸除手動建立的統計資料。Manually created statistics are not dropped.

語法 table_or_view_name . index_name 的目的是要與舊版相容。The syntax table_or_view_name.index_name is maintained for backward compatibility. 您無法利用與舊版相容的語法來卸除 XML 索引或空間索引。An XML index or spatial index cannot be dropped by using the backward compatible syntax.

當卸除含有 128 個 (含) 以上之範圍的索引時,Database EngineDatabase Engine 會延遲取消配置實際的頁面及其相關聯的鎖定,直到認可交易之後。When indexes with 128 extents or more are dropped, the Database EngineDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.

有時候,會卸除再重新建立索引來重新組織或重建索引,例如套用新的填滿因數值,或在大量載入之後重新組織資料。Sometimes indexes are dropped and re-created to reorganize or rebuild the index, such as to apply a new fill factor value or to reorganize data after a bulk load. 若要做到這一點,ALTER INDEX 會比較有效,對於叢集索引而言,尤其如此。To do this, using ALTER INDEXis more efficient, especially for clustered indexes. ALTER INDEX REBUILD 已最佳化,可防止重建非叢集索引所帶來的負擔。ALTER INDEX REBUILD has optimizations to prevent the overhead of rebuilding the nonclustered indexes.

搭配 DROP INDEX 使用選項Using Options with DROP INDEX

您可以在卸除叢集索引時,設定下列索引選項:MAXDOP、ONLINE 和 MOVE TO。You can set the following index options when you drop a clustered index: MAXDOP, ONLINE, and MOVE TO.

請利用 MOVE TO 來卸除叢集索引,再利用單一交易,將結果資料表移到另一個檔案群組或資料分割結構描述。Use MOVE TO to drop the clustered index and move the resulting table to another filegroup or partition scheme in a single transaction.

當您指定 ONLINE = ON 時,DROP INDEX 交易不會封鎖基礎資料和相關聯非叢集索引的查詢和修改。When you specify ONLINE = ON, queries and modifications to the underlying data and associated nonclustered indexes are not blocked by the DROP INDEX transaction. 您只能每次在線上卸除一個叢集索引。Only one clustered index can be dropped online at a time. 如需 ONLINE 選項的完整描述,請參閱 CREATE INDEX (Transact-SQL)For a complete description of the ONLINE option, see CREATE INDEX (Transact-SQL).

如果在檢視上停用了叢集索引,或叢集索引包含分葉層級資料列中的 textntextimagevarchar(max)nvarchar(max)varbinary(max)xml 資料行,您便無法在線上卸除這個叢集索引。You cannot drop a clustered index online if the index is disabled on a view, or contains text, ntext, image, varchar(max), nvarchar(max), varbinary(max), or xml columns in the leaf-level data rows.

利用 ONLINE = ON 和 MOVE TO 選項需要其他暫存磁碟空間。Using the ONLINE = ON and MOVE TO options requires additional temporary disk space.

在卸除索引之後,產生的堆積會出現在 sys.indexes 目錄檢視中,name 資料行會出現 NULL。After an index is dropped, the resulting heap appears in the sys.indexes catalog view with NULL in the name column. 若要檢視資料表名稱,請在 object_id 上,聯結 sys.indexessys.tablesTo view the table name, join sys.indexes with sys.tables on object_id. 如需範例查詢,請參閱 D 範例。For an example query, see example D.

在執行 SQL Server 2005 Enterprise EditionSQL Server 2005 Enterprise Edition 或更新版本的多重處理器電腦上,DROP INDEX 可能會如同其他查詢一樣,使用更多的處理器來執行與卸除叢集索引相關的掃描和排序作業。On multiprocessor computers that are running SQL Server 2005 Enterprise EditionSQL Server 2005 Enterprise Edition or later, DROP INDEX may use more processors to perform the scan and sort operations associated with dropping the clustered index, just like other queries do. 您可以藉由指定 MAXDOP 索引選項,手動設定用來執行 DROP INDEX 陳述式的處理器數目。You can manually configure the number of processors that are used to run the DROP INDEX statement by specifying the MAXDOP index option. 如需詳細資訊,請參閱 設定平行索引作業For more information, see Configure Parallel Index Operations.

當卸除叢集索引時,除非修改了資料分割配置,否則對應的堆積資料分割會保留其資料壓縮設定。When a clustered index is dropped, the corresponding heap partitions retain their data compression setting unless the partitioning scheme is modified. 如果資料分割配置有所變更,所有資料分割都會重建為未壓縮的狀態 (DATA_COMPRESSION = NONE)。If the partitioning scheme is changed, all partitions are rebuilt to an uncompressed state (DATA_COMPRESSION = NONE). 若要卸除叢集索引及變更資料分割配置,您需要執行以下兩個步驟:To drop a clustered index and change the partitioning scheme requires the following two steps:

  1. 卸除叢集索引。Drop the clustered index.

  2. 使用指定壓縮選項的 ALTER TABLE ...REBUILD ... 選項來修改資料表。Modify the table by using an ALTER TABLE ... REBUILD ... option specifying the compression option.

如果在離線狀態卸除叢集索引,則只會移除叢集索引的上層;因此,此作業的速度相當快。When a clustered index is dropped OFFLINE, only the upper levels of clustered indexes are removed; therefore, the operation is quite fast. 線上卸除叢集索引時,SQL ServerSQL Server 會重建堆積兩次,一次在步驟 1,另一次在步驟 2。When a clustered index is dropped ONLINE, SQL ServerSQL Server rebuilds the heap two times, once for step 1 and once for step 2. 如需資料壓縮的詳細資訊,請參閱資料壓縮For more information about data compression, see Data Compression.

XML 索引XML Indexes

當您卸除 XML 索引時,無法指定選項。Options cannot be specified when you drop anXML index. 此外,您無法使用 table_or_view_name . index_name 語法。Also, you cannot use the table_or_view_name.index_name syntax. 當卸除主要 XML 索引時,也會自動卸除所有相關聯的次要 XML 索引。When a primary XML index is dropped, all associated secondary XML indexes are automatically dropped. 如需詳細資訊,請參閱 XML 索引 (SQL Server)For more information, see XML Indexes (SQL Server).

空間索引Spatial Indexes

只有資料表上才支援空間索引。Spatial indexes are supported only on tables. 當您卸除空間索引時,不能指定任何選項或使用 . index_nameWhen you drop a spatial index, you cannot specify any options or use .index_name. 正確的語法如下:The correct syntax is as follows:

DROP INDEX spatial_index_name ON spatial_table_name;DROP INDEX spatial_index_name ON spatial_table_name;

如需空間索引的詳細資訊,請參閱空間索引概觀For more information about spatial indexes, see Spatial Indexes Overview.

權限Permissions

若要執行 DROP INDEX,至少需要擁有對資料表或檢視的 ALTER 權限。To execute DROP INDEX, at a minimum, ALTER permission on the table or view is required. 依預設,這個權限會授與 系統管理員 固定伺服器角色以及 db_ddladmindb_owner 固定資料庫角色。This permission is granted by default to the sysadmin fixed server role and the db_ddladmin and db_owner fixed database roles.

範例Examples

A.A. 卸除一個索引Dropping an index

下列範例會刪除 AdventureWorks2012AdventureWorks2012 資料庫中 ProductVendor 資料表的 IX_ProductVendor_VendorID 索引。The following example deletes the index IX_ProductVendor_VendorID on the ProductVendor table in the AdventureWorks2012AdventureWorks2012 database.

DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO  

B.B. 卸除多個索引Dropping multiple indexes

下列範例會在 AdventureWorks2012AdventureWorks2012 資料庫的單一交易中刪除兩個索引。The following example deletes two indexes in a single transaction in the AdventureWorks2012AdventureWorks2012 database.

DROP INDEX  
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,  
    IX_Address_StateProvinceID ON Person.Address;  
GO  

C.C. 在線上卸除叢集索引或設定 MAXDOP 選項Dropping a clustered index online and setting the MAXDOP option

下列範例將 ONLINE 選項設為 ON,將 MAXDOP 設為 8 來刪除叢集索引。The following example deletes a clustered index with the ONLINE option set to ON and MAXDOP set to 8. 由於未指定 MOVE TO 選項,因此產生的資料表會當做索引儲存在相同的檔案群組中。Because the MOVE TO option was not specified, the resulting table is stored in the same filegroup as the index. 這個範例會使用 AdventureWorks2012AdventureWorks2012 資料庫This examples uses the AdventureWorks2012AdventureWorks2012 database

適用於SQL Server 2008SQL Server 2008 及更新版本、SQL DatabaseSQL DatabaseApplies to: SQL Server 2008SQL Server 2008 and later, SQL DatabaseSQL Database.

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

D.D. 在線上卸除叢集索引或將資料表移到新的檔案群組Dropping a clustered index online and moving the table to a new filegroup

下列範例會在線上刪除叢集索引,並利用 NewGroup 子句,將產生的資料表 (堆積) 移到 MOVE TO 檔案群組。The following example deletes a clustered index online and moves the resulting table (heap) to the filegroup NewGroup by using the MOVE TO clause. 它會查詢 sys.indexessys.tablessys.filegroups 目錄檢視來確認在移動之前和之後,索引和資料表在檔案群組中的位置。The sys.indexes, sys.tables, and sys.filegroups catalog views are queried to verify the index and table placement in the filegroups before and after the move. (從 SQL Server 2016 (13.x)SQL Server 2016 (13.x) 開始,您可以使用 DROP INDEX IF EXISTS 語法。)(Beginning with SQL Server 2016 (13.x)SQL Server 2016 (13.x) you can use the DROP INDEX IF EXISTS syntax.)

適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

--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 AdventureWorks2012  
        ADD FILEGROUP NewGroup;  
    ALTER DATABASE AdventureWorks2012  
        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.E. 在線上卸除 PRIMARY KEY 條件約束Dropping a PRIMARY KEY constraint online

因建立 PRIMARY KEY 或 UNIQUE 條件約束而建立的索引,無法利用 DROP INDEX 來卸除。Indexes that are created as the result of creating PRIMARY KEY or UNIQUE constraints cannot be dropped by using DROP INDEX. 它們是利用 ALTER TABLE DROP CONSTRAINT 陳述式來卸除。They are dropped using the ALTER TABLE DROP CONSTRAINT statement. 如需詳細資訊,請參閱 ALTER TABLEFor more information, see ALTER TABLE.

下列範例會卸除條件約束來刪除含 PRIMARY KEY 條件約束的叢集索引。The following example deletes a clustered index with a PRIMARY KEY constraint by dropping the constraint. ProductCostHistory 資料表沒有 FOREIGN KEY 條件約束。The ProductCostHistory table has no FOREIGN KEY constraints. 如果有的話,您必須先移除這些條件約束。If it did, those constraints would have to be removed first.

-- 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.F. 卸除 XML 索引Dropping an XML index

下列範例會卸除 AdventureWorks2012AdventureWorks2012 資料庫中 ProductModel 資料表的 XML 索引。The following example drops an XML index on the ProductModel table in the AdventureWorks2012AdventureWorks2012 database.

DROP INDEX PXML_ProductModel_CatalogDescription   
    ON Production.ProductModel;  

G.G. 卸除 FILESTREAM 資料表上的叢集索引Dropping a clustered index on a FILESTREAM table

下列範例會在線上刪除叢集索引,並將結果資料表 (堆積) 和 FILESTREAM 資料移到 MyPartitionScheme 資料分割配置,其方式是同時使用 MOVE TO 子句和 FILESTREAM ON 子句。The following example deletes a clustered index online and moves the resulting table (heap) and FILESTREAM data to the MyPartitionScheme partition scheme by using both the MOVE TO clause and the FILESTREAM ON clause.

適用對象SQL Server 2008SQL Server 2008 及更新版本。Applies to: SQL Server 2008SQL Server 2008 and later.

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

另請參閱See Also

ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
ALTER PARTITION SCHEME (Transact-SQL) ALTER PARTITION SCHEME (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL) CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL) CREATE PARTITION SCHEME (Transact-SQL)
CREATE SPATIAL INDEX (Transact-SQL) CREATE SPATIAL INDEX (Transact-SQL)
CREATE XML INDEX (Transact-SQL) CREATE XML INDEX (Transact-SQL)
EVENTDATA (Transact-SQL) EVENTDATA (Transact-SQL)
sys.indexes (Transact-SQL) sys.indexes (Transact-SQL)
sys.tables (Transact-SQL) sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL) sys.filegroups (Transact-SQL)
sp_spaceused (Transact-SQL)sp_spaceused (Transact-SQL)