DROP INDEX (Transact-SQL)

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

1 つ以上のリレーショナル インデックスまたは XML インデックスを現在のデータベースから削除します。SQL Server 2005 では、MOVE TO オプションを指定することで、クラスタ化インデックスを削除し、結果のテーブルを別のファイル グループまたはパーティション構成に移動するという操作を、単一のトランザクションで実行できます。

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

Transact-SQL 構文表記規則

構文

DROP INDEX
{ <drop_relational_or_xml_index> [ ,...n ] 
| <drop_backward_compatible_index> [ ,...n ]
}

<drop_relational_or_xml_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" 
            }
}

引数

• 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 プロセッサです。

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

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

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

  メモ :
  並列インデックス操作は、SQL Server 2005 Enterprise Edition でのみ利用できます。

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

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

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

  メモ :
  オンライン インデックス操作は、SQL Server 2005 Enterprise Edition でのみ利用できます。

• MOVE TO
  現在クラスタ化インデックスのリーフ レベルにあるデータ行を移動する場所を指定します。データは、ヒープの形式で新しい場所に移動されます。新しい場所としてパーティション構成またはファイル グループを指定できますが、このパーティション構成やファイル グループはあらかじめ存在している必要があります。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)」を参照してください。

解説

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

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

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

index_name ON { table_or_view_name } という構文は、SQL Server 2005 で新しく追加された構文です。table_or_view_name**.**index_name という構文は、旧バージョンとの互換性を維持するためのものです。単一のトランザクションで両方のオプションを組み合わせると、ステートメントがエラーになります。XML インデックスは、旧バージョンとの互換性のための構文を使用して削除することはできません。

128 以上のエクステントを持つインデックスを削除すると、SQL Server 2005 データベース エンジンは、トランザクションがコミットされるまで実際のページの割り当て解除と関連するロックを遅らせます。詳細については、「ラージ オブジェクトの削除と再構築」を参照してください。

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

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

SQL Server 2005 では、クラスタ化インデックスを削除する際に、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 でより多くのプロセッサが使用される可能性があります。DROP INDEX ステートメントを実行するために使用するプロセッサの数を手動で構成するには、MAXDOP インデックス オプションを指定します。詳細については、「並列インデックス操作の構成」を参照してください。

XML インデックス

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

権限

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

参照

関連項目

ALTER PARTITION SCHEME (Transact-SQL)
ALTER INDEX (Transact-SQL)
ALTER TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
CREATE PARTITION SCHEME (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.indexes (Transact-SQL)
sys.tables (Transact-SQL)
sys.filegroups (Transact-SQL)
sp_spaceused (Transact-SQL)

その他の技術情報

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

ヘルプおよび情報

SQL Server 2005 の参考資料の入手