DROP INDEX (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 でのオプションの使用

XML インデックス

空間インデックス

権限

DROP INDEX を実行するには、少なくともテーブルまたはビューに対する ALTER 権限が必要です。この権限は、固定サーバー ロール sysadmin と、固定データベース ロール db_ddladmin および db_owner に既定で許可されています。

例

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

USE AdventureWorks;
GO
DROP INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor;
GO

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

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. クラスタ化インデックスをオンラインで削除し、テーブルを新しいファイル グループに移動する

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 制約をオンラインで削除する

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 インデックスを削除する

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

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

USE MyDatabase;
GO
DROP INDEX PK_MyClusteredIndex 
    ON dbo.MyTable 
    MOVE TO MyPartitionScheme
    FILESTREAM_ON MyPartitionScheme;
GO

関連項目

参照

概念

その他の技術情報