DROP INDEX (Transact-SQL)

適用対象: SQL Server Azure SQL データベース Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

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

重要

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

Transact-SQL 構文表記規則

構文

SQL Server の構文 (ファイル グループと filestream を除くすべてのオプションが 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" } ]
}

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 }

Azure Synapse Analytics および Analytics Platform System (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 では、database_nameが現在のデータベースであるか、database_nameがtempdbされ、object_nameが#で始まる場合にdatabase_name.[schema_name].object_nameという 3 つの部分から構成される名前形式がサポートされています。

<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 構成オプションをオーバーライドします。 詳細については、「 並列処理の最大限度の構成 (サーバー構成オプション)」を参照してください。 並列プランの実行で使用されるプロセッサの数を制限するには、 MAXDOP を使用します。 最大数は 64 プロセッサです。

重要

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

max_degree_of_parallelism には、次のいずれかの値を指定できます。

Value	説明
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"を指定する場合は、現在のセッションにONQUOTED_IDENTIFIERオプションを設定する必要があります。 これが既定の設定です。 詳しくは、「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 データの既定の位置を指定します。

Note

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

解説

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

Note

ドキュメントでは、一般的にインデックスを参照して B ツリーという用語を使用します。 行ストア インデックスでは、データベース エンジンは 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を使用してクラスター化インデックスを削除し、結果のテーブルを 1 つのトランザクション内の別のファイル グループまたはパーティション構成に移動します。

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

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

ONLINE = ONオプションとMOVE TOオプションを使用するには、より多くの一時的なディスク領域が必要です。

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

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

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

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

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

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

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 に既定で許可されています。

例

この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。

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

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

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 オプションが指定されていないため、結果のテーブルはインデックスと同じファイル グループに格納されます。

適用対象: 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.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 制約をオンラインで削除する

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
• sys.tables
• sys.filegroups
• sp_spaceused