DROP INDEX (Transact-SQL)

[アーティクル]
05/17/2024

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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

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

重要

定義されている <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 }
[ ; ]

Note

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

引数

IF EXISTS

適用対象: SQL Server 2016 (13.x) 以降のバージョン。

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

index_name

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

database_name

データベースの名前。

schema_name

テーブルまたはビューが属するスキーマの名前です。

table_or_view_name

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

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

Azure SQL Database では、3 部構成の名前形式 database_name.[schema_name].object_name がサポートされます。現在の database_name データベースであるか、database_nameで tempdb 始まり、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 構成オプションをオーバーライドします。詳細については、「並列処理の最大限度の構成 (サーバー構成オプション)」を参照してください。並列プランの実行で使用されるプロセッサの数を制限するために使用 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 の各エディションとサポートされている機能」を参照してください。

{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」をご覧ください。

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

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 でオプションを使用する

クラスター化インデックスを削除するときに、次のインデックスオプションを設定できます。 MAXDOPONLINEMOVE TO

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

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

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

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

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

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

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

クラスター化インデックスを削除します。
圧縮オプションを指定するオプションを ALTER TABLE ... REBUILD ... 使用して、テーブルを変更します。

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

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 サンプルデータベースを使用します。このサンプルデータベースは、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 制約をオンラインで削除する

作成またはUNIQUE制約の結果として作成PRIMARY KEYされたインデックスは、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

Share via

DROP INDEX (Transact-SQL)

構文

引数

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 テーブルにクラスター化インデックスを削除する

フィードバック

フィードバック

その他のリソース

Share via

DROP INDEX (Transact-SQL)

構文

引数

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 テーブルにクラスター化インデックスを削除する

関連するコンテンツ

フィードバック

フィードバック

その他のリソース

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