DROP INDEX (Transact-SQL)

[アーティクル]
06/18/2013

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_index> で指定されている構文を使用してください。 XML インデックスは、旧バージョンとの互換性のための構文を使用して削除することはできません。

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

トピックリンクアイコン Transact-SQL 構文表記規則

構文

DROP INDEX
{ <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 ] . | schema_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" } ]
}

引数

index_name
削除するインデックスの名前です。
database_name
データベースの名前を指定します。
schema_name
テーブルまたはビューが属するスキーマの名前です。
table_or_view_name
インデックスに関連付けられているテーブルまたはビューの名前を指定します。空間インデックスはテーブルでのみサポートされます。

オブジェクトに対するインデックスのレポートを表示するには、sys.indexes カタログビューを使用します。
<drop_clustered_index_option>
クラスター化インデックスオプションを制御します。これらのオプションは、他のインデックス型では使用できません。

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

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

max_degree_of_parallelism には次のデータを指定できます。

1
並列プランを生成しないようにします。
>1
並列インデックス操作で使用するプロセッサの最大数を、指定された数に制限します。
0 (既定値)
現在のシステムワークロードに基づいて、実際の数以下のプロセッサを使用します。

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

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

ONLINE = ON | OFF
インデックス操作中に、基となるテーブルとそれに関連する各インデックスに対してクエリやデータ変更を行うことができるかどうかを指定します。既定値は OFF です。

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

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

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

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

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

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

構成内の列名は、インデックス定義内の列に制限されません。ベーステーブルの任意の列を指定できます。
filegroup_name
結果のテーブルのための場所として、ファイルグループを指定します。場所を指定しないでテーブルをパーティション分割すると、結果のテーブルはクラスター化インデックスと同じファイルグループに格納されます。ファイルグループはあらかじめ存在している必要があります。

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

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

ここでは、default はキーワードではありません。 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" }
現在クラスター化インデックスのリーフレベルに格納されている 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 はキーワードではありません。 default は、既定ファイルグループの識別子のため、MOVE TO "default" または MOVE TO [default] のように区切る必要があります。 "default" を指定する場合は、現在のセッションの QUOTED_IDENTIFIER オプションが ON である必要があります。これは既定の設定です。詳細については、「SET QUOTED_IDENTIFIER (Transact-SQL)」を参照してください。

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

説明

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

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

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

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)」を参照してください。

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

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

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

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

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

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

OFFLINE でクラスター化インデックスを削除すると、クラスター化インデックスの上位レベルだけが削除されます。そのため、操作はとても高速です。 ONLINE でクラスター化インデックスを削除すると、SQL Server によって、ヒープが手順 1. で 1 回、手順 2. で 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 権限が必要です。この権限は、固定サーバーロール sysadmin と、固定データベースロール db_ddladmin および db_owner に既定で許可されています。

使用例

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

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

USE AdventureWorks2012;
GO
DROP INDEX IX_ProductVendor_BusinessEntityID 
    ON Purchasing.ProductVendor;
GO

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

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

USE AdventureWorks2012;
GO
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 2005 Enterprise Edition 以降でのみ実行できます。

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

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

次の例では、クラスター化インデックスをオンラインで削除し、MOVE TO 句を使用することで、結果のテーブル (ヒープ) をファイルグループ NewGroup に移動します。移動の前後で sys.indexes、sys.tables、および sys.filegroups カタログビューを参照し、ファイルグループ内のインデックスとテーブルの配置を確認します。

USE AdventureWorks2012;
GO
--Create a clustered index on the PRIMARY filegroup if the index does not exist.
IF NOT EXISTS (SELECT name FROM sys.indexes WHERE name = 
            N'AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate')
    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\MSSQL10.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 制約はありません。 FOREIGN KEY 制約がある場合には、まずそれを削除する必要があります。

USE AdventureWorks2012;
GO
-- 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);

GO

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

次の例では、ProductModel テーブル上の XML インデックスを削除します。

USE AdventureWorks2012;
GO
DROP INDEX PXML_ProductModel_CatalogDescription 
    ON Production.ProductModel;
GO

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

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

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

次の方法で共有

DROP INDEX (Transact-SQL)

構文

引数

説明

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

XML インデックス

空間インデックス

権限

使用例

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

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

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

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

E. PRIMARY KEY 制約をオンラインで削除する

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

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

関連項目

参照

その他のリソース

次の方法で共有

DROP INDEX (Transact-SQL)

構文

引数

説明

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

XML インデックス

空間インデックス

権限

使用例

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

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

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

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

E. PRIMARY KEY 制約をオンラインで削除する

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

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

関連項目

参照

その他のリソース

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