DROP INDEX (Transact-SQL)

 

適用対象: ○SQL Server (2008 以降)○Azure SQL Database○Azure SQL Data Warehouse ○Parallel Data Warehouse

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

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

System_CAPS_ICON_important.jpg 重要


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

Topic link icon Transact-SQL 構文表記規則

-- Syntax for SQL Server (All options except filegroup and filestream apply to Azure SQL Database Code-named Sterling Preview.)  
  
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 ] . | 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" } ]  
}  

-- Syntax for 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 ] . | schema_name. ]   
    table_or_view_name  
}  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
DROP INDEX index_name ON [ database_name . [schema_name ] . | schema_name . ] table_name  
[;]  

場合に存在します。

適用対象: SQL Server (SQL Server 2016 から 現在のバージョンまで)。

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

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

データベース名
データベースの名前です。

schema_name
テーブルまたはビューが属するスキーマの名前を指定します。

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

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

Windows Azure SQL データベースでは、database_name が現在のデータベースの場合、または database_name が tempdb で、object_name が # で始まる場合に、3 つの要素で構成された名前形式 database_name.[schema_name].object_name をサポートします。

<drop_clustered_index_option>

Applies to: SQL Server 2008: through SQL Server 2016, SQL データベース.

クラスター化インデックス オプションを制御します。 これらのオプションは、他のインデックス型では使用できません。

MAXDOP = max_degree_of_parallelism

適用対象:SQL Server 2008:を通じてSQL Server 2016、 SQL データベース (パフォーマンス レベル P2 および P3 のみ)。

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

System_CAPS_ICON_important.jpg 重要


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

max_degree_of_parallelismを指定できます。

1
並列プラン生成を抑制します。

>1
並列インデックス操作で使用される最大プロセッサ数を、指定した数に制限します。

0 (既定)
現在のシステム ワークロードに基づいて、実際の数以下のプロセッサを使用します。

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

System_CAPS_ICON_note.jpg メモ


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

ONLINE = ON |OFF

Applies to: SQL Server 2008: through SQL Server 2016, Azure SQL データベース.

インデックス操作時に、基になるテーブルや関連するインデックスをクエリやデータ変更で使用できるかどうかを指定します。 既定値は OFF です。

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

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

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

System_CAPS_ICON_note.jpg メモ


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

MOVE TO { partition_scheme_name(column_name) | filegroup_name | "default"

適用対象: SQL Server 2008: から SQL Server 2016 SQL データベースファイル グループの名前として"default"をサポートします。

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

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

partition_scheme_name ( column_name )

Applies to: SQL Server 2008: through SQL Server 2016, SQL データベース.

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

構成内の列名は、インデックス定義内の列に制限されません。 ベース テーブルの任意の列を指定できます。

filegroup_name

適用対象: SQL Server 2008: から SQL Server 2016

結果のテーブルのための場所として、ファイル グループを指定します。 場所を指定しないでテーブルをパーティション分割すると、結果のテーブルはクラスター化インデックスと同じファイル グループに格納されます。 ファイル グループは既に存在している必要があります。

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

System_CAPS_ICON_note.jpg メモ


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

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "既定" }

適用対象: SQL Server 2008: から SQL Server 2016

現在クラスター化インデックスのリーフ レベルに格納されている 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 データの既定の位置を指定します。

System_CAPS_ICON_note.jpg メモ


ここでは、default はキーワードではありません。 既定のファイル グループの識別子を指定し、MOVE TO ように区切る必要があります"既定"または MOVE TO [既定]します。 "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 は、非クラスター化インデックスを再構築するオーバーヘッドをなくすために最適化されています。

クラスター化インデックスを削除する際に、MAXDOP、ONLINE、および MOVE TO インデックス オプションを設定できます。

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

ONLINE = ON を指定すると、基となるデータや関連する非クラスター化インデックスに対するクエリと変更は、DROP INDEX トランザクションによってブロックされません。 オンラインでは、一度に&1; つのクラスター化インデックスしか削除できません。 ONLINE オプションの詳細については、次を参照してください。 CREATE INDEX (TRANSACT-SQL)します。

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

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

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

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

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

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

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

OFFLINE でクラスター化インデックスを削除すると、クラスター化インデックスの上位レベルだけが削除されます。そのため、操作はとても高速です。 クラスター化インデックスがオンラインで削除されるときSQL Server手順 1. で手順 2 の 1 回、ヒープ、2 回を再構築します。 データ圧縮に関する詳細については、次を参照してください。データ圧縮します。

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 に既定で許可されています。

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

次の例は、インデックスを削除IX_ProductVendor_VendorID上、ProductVendorテーブルに、AdventureWorks2012データベースです。

DROP INDEX IX_ProductVendor_BusinessEntityID   
    ON Purchasing.ProductVendor;  
GO  
  

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

次の例では、AdventureWorks2012 データベースから、単一のトランザクションで&2; つのインデックスを削除します。

DROP INDEX  
    IX_PurchaseOrderHeader_EmployeeID ON Purchasing.PurchaseOrderHeader,  
    IX_Address_StateProvinceID ON Person.Address;  
GO  
  

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

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

Applies to: SQL Server 2008: through SQL Server 2016, SQL データベース.
DROP INDEX AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate   
    ON Production.BillOfMaterials WITH (ONLINE = ON, MAXDOP = 2);  
GO  
  

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

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

適用対象: SQL Server 2008: から SQL Server 2016
--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\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 制約をオンラインで削除する

PRIMARY KEY 制約や UNIQUE 制約の結果作成されたインデックスは、DROP INDEX で削除することができません。 そのようなインデックスは、ALTER TABLE DROP CONSTRAINT ステートメントを使用して削除します。 詳細については、次を参照してください。 ALTER TABLEします。

次の例では、制約を削除することで、PRIMARY KEY 制約によるクラスター化インデックスを削除します。 ProductCostHistoryテーブルに FOREIGN KEY 制約がありません。 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 インデックスを削除する

次の例では XML インデックスを削除、ProductModelテーブルに、AdventureWorks2012データベースです。

DROP INDEX PXML_ProductModel_CatalogDescription   
    ON Production.ProductModel;  
  

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

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

適用対象: SQL Server 2008: から SQL Server 2016
USE MyDatabase;  
GO  
DROP INDEX PK_MyClusteredIndex   
    ON dbo.MyTable   
    WITH (MOVE TO MyPartitionScheme,  
          FILESTREAM_ON MyPartitionScheme);  
GO  

H. テーブルにインデックスを削除します。

次の例は、インデックスを削除VendorIDIndex上、ProductVendorテーブルです。

DROP INDEX VendorIDIndex ON ProductVendor;  

ALTER INDEX (TRANSACT-SQL)
ALTER PARTITION SCHEME (TRANSACT-SQL)
ALTER TABLE (TRANSACT-SQL)
CREATE INDEX (TRANSACT-SQL)
パーティション構成 (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)

コミュニティの追加

追加
表示: