DROP INDEX (Transact-SQL)

DROP INDEX ステートメントを使用すると、現在の SQL Server データベースからインデックスを削除できます。オブジェクト エクスプローラを使用してインデックスを削除する方法については、「インデックスを削除する方法 (SQL Server Management Studio)」を参照してください。

DROP INDEX ステートメントを使用して、PRIMARY KEY 制約または UNIQUE 制約を含むインデックスを削除することはできません。制約を削除してからインデックスを削除するには、ALTER TABLE を DROP CONSTRAINT 句と共に使用します。

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

重要
<drop_backward_compatible_index> で定義される構文は、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 (既定値)
    現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。

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

  注意
  並列インデックス操作は、SQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

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

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

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

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

  注意
  オンラインでのインデックス操作は、SQL Server Enterprise Edition、Developer Edition、および Evaluation Edition でのみ使用できます。

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

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

説明

非クラスタ化インデックスを削除すると、インデックス定義がメタデータから削除され、インデックス データ ページ (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 つの手順を実行します。

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

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

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

XML インデックス

XML インデックスを削除する際には、オプションを指定できません。また、table_or_view_name**.**index_name 構文も使用できません。プライマリ XML インデックスを削除すると、関連するすべてのセカンダリ XML インデックスが自動的に削除されます。詳細については、「XML データ型の列のインデックス」を参照してください。

空間インデックス

空間インデックスはテーブルでのみサポートされます。空間インデックスを削除する場合、オプションを指定することも、**.**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 AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

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

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

USE AdventureWorks;
GO
DROP INDEX
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,
    IX_VendorAddress_AddressID ON Purchasing.VendorAddress;
GO

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

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

注意
この例は、SQL Server 2005 Enterprise Edition 以降でのみ実行できます。

USE AdventureWorks;
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 AdventureWorks;
GO
--Create a clustered index on the PRIMARY filegroup if it 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.
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
                  FROM master.sys.master_files
                  WHERE database_id = 1 AND file_id = 1);

-- execute the ALTER DATABASE statement 
IF NOT EXISTS (SELECT name FROM sys.filegroups
                WHERE name = N'NewGroup')
    BEGIN
    ALTER DATABASE AdventureWorks
        ADD FILEGROUP NewGroup;
    EXECUTE ('ALTER DATABASE AdventureWorks
        ADD FILE (NAME = File1,
            FILENAME = '''+ @data_path + '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 AdventureWorks;
GO
-- Set ONLINE = OFF to execute this example on editions other than Enterprise Edition.
ALTER TABLE Production.ProductCostHistory
    DROP CONSTRAINT PK_ProductCostHistory_ProductID_StartDate
        WITH (ONLINE = ON);
GO

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

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

USE AdventureWorks;
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 
    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)

概念

インデックスの削除

その他の技術情報

インデックスに必要なディスク領域の決定