DROP INDEX (Transact-SQL)

適用対象:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

1 つ以上のリレーショナル インデックス、空間インデックス、フィルター選択されたインデックス、または XML インデックスを現在のデータベースから削除します。 MOVE TO オプションを指定すると、1 つのトランザクションで、クラスター化インデックスを削除し、その結果生成されたテーブルを別のファイル グループまたはパーティション構成に移動できます。

DROP INDEX ステートメントは、PRIMARY KEY 制約または UNIQUE 制約を定義することで作成されたインデックスには適用されません。 制約および対応するインデックスを削除するには、ALTER TABLE を DROP CONSTRAINT 句と共に使用します。

重要

<drop_backward_compatible_index> で定義される構文は、Microsoft SQL Server の今後のバージョンでは削除される予定です。 新規の開発作業ではこの構文を使用しないようにし、現在この機能を使用しているアプリケーションは修正することを検討してください。 代わりに、<drop_relational_or_xml_or_spatial_index> で指定されている構文を使用してください。 XML インデックスは、旧バージョンとの互換性のための構文を使用して削除することはできません。

Transact-SQL 構文表記規則

構文

-- 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 Synapse Analytics and Parallel Data Warehouse  
  
DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }  
[;]  

Note

SQL Server 2014 (12.x) 以前のバージョンの Transact-SQL 構文を確認するには、以前のバージョンのドキュメントを参照してください。

引数

IF EXISTS
適用対象: SQL Server (SQL Server 2016 (13.x) から現在のバージョンまで)。

条件付きでは既に存在する場合にのみ、インデックスを削除します。

index_name
削除するインデックスの名前です。

database_name
データベースの名前です。

schema_name
テーブルまたはビューが属するスキーマの名前を指定します。

table_or_view_name
インデックスに関連付けられているテーブルまたはビューの名前です。 空間インデックスはテーブルでのみサポートされます。

オブジェクトに対するインデックスのレポートを表示するには、sys.indexes カタログ ビューを使用します。

Azure SQL Database では、database_name が現在のデータベースの場合、または database_name が tempdb で、object_name が # で始まる場合に、3 つの要素で構成された名前形式 database_name.[schema_name].object_name がサポートされます。

<drop_clustered_index_option>
適用対象: SQL Server 2008 (10.0.x) 以降、SQL Database。

クラスター化インデックス オプションを制御します。 これらのオプションは、他のインデックス型では使用できません。

MAXDOP = max_degree_of_parallelism
適用対象: SQL Server 2008 (10.0.x) 以降、SQL Database (パフォーマンス レベル P2 および P3 のみ)。

インデックス操作の間、max degree of parallelism 構成オプションをオーバーライドします。 詳細については、「 max degree of parallelism サーバー構成オプションの構成」を参照してください。 並列プランの実行で使用されるプロセッサ数を制限するには、MAXDOP を使用します。 最大数は 64 プロセッサです。

重要

MAXDOP は、空間インデックスまたは XML インデックスには使用できません。

max_degree_of_parallelism は次のように指定できます。

1
並列プラン生成を抑制します。

>1
並列インデックス操作で使用される最大プロセッサ数を、指定した数に制限します。

0 (既定値)
現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。

詳細については、「 並列インデックス操作の構成」を参照してください。

Note

並列インデックス操作は、SQL Server の一部のエディションで使用できません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

ONLINE = ON | OFF
適用対象: SQL Server 2008 (10.0.x) 以降、Azure SQL Database。

インデックス操作時に、基になるテーブルや関連するインデックスをクエリやデータ変更で使用できるかどうかを指定します。 既定値は OFF です。

ON
長期間のテーブル ロックは持続されません。 これにより、基となるテーブルに対してクエリや更新を続けることができます。

OFF
テーブル ロックが適用され、インデックス操作中はテーブルが利用できません。

クラスター化インデックスを削除するときには、ONLINE オプションだけを指定できます。 詳細については、「解説」を参照してください。

Note

オンラインでのインデックス操作は、 SQL Serverのすべてのエディションで使用できるわけではありません。 SQL Server の各エディションでサポートされる機能の一覧については、「SQL Server 2022 の各エディションとサポートされている機能」を参照してください。

MOVE TO { partition_scheme_name(column_name) | filegroup_name | "default"
適用対象: SQL Server 2008 (10.0.x) 以降。 SQL Database は、ファイル グループの名前として "default" をサポートしています。

現在クラスター化インデックスのリーフ レベルにあるデータ行を移動する場所を指定します。 データは、ヒープの形式で新しい場所に移動されます。 新しい場所としてパーティション構成またはファイル グループを指定できますが、このパーティション構成やファイル グループはあらかじめ存在している必要があります。 MOVE TO は、インデックス付きビューまたは非クラスター化インデックスに対しては有効ではありません。 パーティション構成やファイル グループを指定しないと、結果のテーブルは、クラスター化インデックスに対して定義されているのと同じパーティション構成またはファイル グループに配置されます。

MOVE TO を使用してクラスター化インデックスを削除すると、ベース テーブル上の非クラスター化インデックスが再構築されますが、元のファイル グループまたはパーティション構成からは移動されません。 ベース テーブルを別のファイル グループやパーティション構成に移動しても、非クラスター化インデックスは、ベース テーブルの新しい場所 (ヒープ) に同時に移動されません。 したがって、以前に非クラスター化インデックスがクラスター化インデックスに対応した位置にあっても、ヒープとは対応しなくなる可能性があります。 パーティション インデックスの位置合わせの詳細については、「パーティション テーブルとパーティション インデックス」を参照してください。

partition_scheme_name(column_name)
適用対象: SQL Server 2008 (10.0.x) 以降、SQL Database。

結果のテーブルのための場所として、パーティション構成を指定します。 パーティション構成は、CREATE PARTITION SCHEME または ALTER PARTITION SCHEME のどちらかを実行して、あらかじめ作成しておく必要があります。 場所を指定しないでテーブルをパーティション分割すると、テーブルは既存のクラスター化インデックスと同じパーティション構成に格納されます。

構成内の列名は、インデックス定義内の列に制限されません。 ベース テーブルの任意の列を指定できます。

filegroup_name
適用対象: SQL Server 2008 (10.0.x) 以降。

結果のテーブルのための場所として、ファイル グループを指定します。 場所を指定しないでテーブルをパーティション分割すると、結果のテーブルはクラスター化インデックスと同じファイル グループに格納されます。 ファイル グループは既に存在している必要があります。

"default"
結果のテーブルの既定の場所を指定します。

Note

このコンテキストでは、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" }
適用対象: 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 データの既定の位置を指定します。

Note

このコンテキストでは、default はキーワードではありません。 これは、既定のファイル グループの識別子で、MOVE TO "default" または MOVE TO [default] のように区切り記号で区切る必要があります。 "default" を指定する場合は、現在のセッションに対して QUOTED_IDENTIFIER オプションが ON である必要があります。 これが既定の設定です。 詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

解説

非クラスター化インデックスを削除すると、インデックス定義がメタデータから削除され、インデックス データ ページ (B ツリー) がデータベース ファイルから削除されます。 クラスター化インデックスを削除すると、インデックス定義がメタデータから削除され、クラスター化インデックスのリーフ レベルに格納されたデータ行は、結果の順序付けられていないテーブル (ヒープ) に格納されます。 それまでインデックスが使用していたすべての領域は解放されます。 この領域は、任意のデータベース オブジェクトに使用できます。

Note

SQL Server のドキュメントでは、一般にインデックスに関して B ツリーという用語が使用されます。 行ストア インデックスで、SQL Server によって B+ ツリーが実装されます。 これは、列ストア インデックスやメモリ内データ ストアには適用されません。 詳細については、「SQL Server と Azure SQL のインデックスのアーキテクチャとデザイン ガイド」を参照してください。

インデックスが格納されているファイル グループがオフラインであるか読み取り専用に設定されている場合には、インデックスを削除することはできません。

インデックス付きビューのクラスター化インデックスを削除すると、同じビューのすべての非クラスター化インデックスと自動作成された統計情報が自動的に削除されます。 手動で作成した統計情報は削除されません。

構文 table_or_view_name.index_name は、旧バージョンとの互換性のために維持されます。 XML インデックスまたは空間インデックスは、旧バージョンとの互換性のための構文を使用して削除することはできません。

128 以上のエクステントを持つインデックスを削除すると、トランザクションがコミットされるまで、実際のページの割り当て解除および関連付けられているロックが、データベース エンジンによって延期されます。

新しい FILL FACTOR 値を適用したり、一括読み取りの後でデータを再構成するためなどに、インデックスを削除し、作り直して、再構成または再構築することがあります。 これを行うには、特にクラスター化インデックスに対しては、ALTER INDEX を使用するのがより効率的です。 ALTER INDEX REBUILD は、非クラスター化インデックスを再構築するオーバーヘッドをなくすために最適化されています。

DROP INDEX でのオプションの使用

クラスター化インデックスを削除する際に、MAXDOP、ONLINE、MOVE TO インデックス オプションを設定できます。

MOVE TO は、単一のトランザクションでクラスター化インデックスを削除し、結果のテーブルを別のファイル グループまたはパーティション構成に移動するために使用します。

ONLINE = ON を指定すると、基となるデータや関連する非クラスター化インデックスに対するクエリと変更は、DROP INDEX トランザクションによってブロックされません。 オンラインでは、一度に 1 つのクラスター化インデックスしか削除できません。 ONLINE オプションの詳細な説明については、「CREATE INDEX (Transact-SQL)」を参照してください。

インデックスがビュー上で無効になっているか、リーフ レベルのデータ行に textntextimagevarchar(max)nvarchar(max)varbinary(max) 、または xml 列を含む場合には、クラスター化インデックスをオンラインで削除することはできません。

ONLINE = ON オプションおよび MOVE TO オプションを使用するには、追加の一時ディスク領域が必要です。

インデックスを削除すると、結果のヒープは、sys.indexes カタログ ビューで name 列が NULL として表示されます。 テーブル名を表示するには、sys.indexessys.tablesobject_id で結合します。 クエリの例については、例 D を参照してください。

SQL Server 2005 Enterprise Edition 以降を実行するマルチプロセッサ コンピューターでは、他のクエリと同様に、クラスター化インデックスの削除に関連するスキャン操作や並べ替え操作の実行に、DROP INDEX がより多くのプロセッサを使用する場合があります。 MAXDOP インデックス オプションを指定すると、DROP INDEX ステートメントの実行に使用されるプロセッサ数を手動で構成できます。 詳細については、「 並列インデックス操作の構成」を参照してください。

クラスター化インデックスを削除する場合、パーティション構成を変更しない限り、対応するヒープ パーティションでデータ圧縮設定が維持されます。 パーティション構成を変更すると、すべてのパーティションが圧縮されていない状態に再構築されます (DATA_COMPRESSION = NONE)。 クラスター化インデックスを削除し、パーティション構成を変更するには、次の 2 つの手順が必要です。

  1. クラスター化インデックスを削除します。

  2. 圧縮オプションを指定する ALTER TABLE ...REBUILD ... オプションを使用して、テーブルを変更します。

OFFLINE でクラスター化インデックスを削除すると、クラスター化インデックスの上位レベルだけが削除されます。そのため、操作はとても高速です。 ONLINE でクラスター化インデックスを削除すると、SQL Server によって、ヒープが手順 1 で 1 回、手順 2 で 1 回の計 2 回再構築されます。 データ圧縮の詳細については、「データの圧縮」を参照してください。

XML インデックス

AnXML インデックスを削除するときに、オプションを指定することはできません。 また、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 権限が必要です。 この権限は、固定サーバー ロール sysadmin と、固定データベース ロール db_ddladmin および db_owner に既定で許可されています。

A. インデックスを削除する

次の例では、AdventureWorks2022 データベース内の ProductVendor テーブルで、インデックス IX_ProductVendor_VendorID を削除します。

DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO  

B. 複数のインデックスを削除する

次の例では、AdventureWorks2022 データベース内の単一のトランザクションで、2 つのインデックスを削除します。

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

C. クラスター化インデックスをオンラインで削除し、MAXDOP オプションを設定する

次の例では、ONLINE オプションに ON を設定し、MAXDOP オプションに 8 を設定してクラスター化インデックスを削除します。 MOVE TO オプションは指定していないため、結果のテーブルは、インデックスと同じファイル グループに格納されます。 この例では、AdventureWorks2022 データベースを使用します

適用対象: SQL Server 2008 (10.0.x) 以降、SQL Database。

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

D. クラスター化インデックスをオンラインで削除し、テーブルを新しいファイル グループに移動する

次の例では、クラスター化インデックスをオンラインで削除し、 NewGroup 句を使用することで、結果のテーブル (ヒープ) をファイル グループ MOVE TO に移動します。 移動の前後で sys.indexessys.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 制約をオンラインで削除する

PRIMARY KEY 制約や UNIQUE 制約の結果作成されたインデックスは、DROP INDEX を使用して削除することができません。 そのようなインデックスは、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 データベース内の ProductModel テーブルで、XML インデックスを削除します。

DROP INDEX PXML_ProductModel_CatalogDescription   
    ON Production.ProductModel;  

G. FILESTREAM テーブルのクラスター化インデックスを削除する

次の例では、クラスター化インデックスをオンラインで削除し、MyPartitionScheme 句と MOVE TO 句の両方を使用して、結果のテーブル (ヒープ) と FILESTREAM データを FILESTREAM ON パーティション構成に移動します。

適用対象: SQL Server 2008 (10.0.x) 以降。

DROP INDEX PK_MyClusteredIndex   
    ON dbo.MyTable   
    WITH (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)