DROP INDEX(Transact-SQL)

적용 대상: SQL Server Azure SQL 데이터베이스 Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW)

현재 데이터베이스에서 하나 이상의 관계형 인덱스, 공간 인덱스, 필터링된 인덱스 또는 XML 인덱스를 제거합니다. 옵션을 지정하여 클러스터형 인덱스 삭제 및 결과 테이블을 단일 트랜잭션의 다른 파일 그룹 또는 파티션 구성표로 이동할 수 있습니다 MOVE TO .

문은 DROP INDEX 정의 PRIMARY KEY 또는 UNIQUE 제약 조건으로 만든 인덱스에는 적용되지 않습니다. 제약 조건 및 해당 인덱스 제거하려면 절과 함께 ALTER TABLE을 DROP CONSTRAINT 사용합니다.

Transact-SQL 구문 표기 규칙

구문

SQL Server 구문(파일 그룹 및 파일 스트림을 제외한 모든 옵션은 Azure SQL Database에 적용됨).

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.table_or_view_name | schema_name.table_or_view_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" } ]
}

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

Azure Synapse Analytics 및 Analytics Platform System(PDW)에 대한 구문입니다.

DROP INDEX index_name ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
[ ; ]

인수

IF EXISTS

적용 대상: SQL Server 2016(13.x) 이상 버전

이미 있는 경우에만 인덱스를 조건부로 삭제합니다.

index_name

삭제할 인덱스의 이름입니다.

database_name

데이터베이스의 이름입니다.

schema_name

테이블이나 뷰가 속한 스키마의 이름입니다.

table_or_view_name

인덱스와 관련된 테이블이나 뷰의 이름입니다. 공간 인덱스는 테이블에서만 지원됩니다.

개체에 대한 인덱스 보고서를 표시하려면 sys.indexes 카탈로그 뷰를 사용합니다.

Azure SQL Database는 현재 데이터베이스이거나 database_name object_name 시작하는 세 부분으로 구성된 이름 형식 database_name.[schema_name].object_name database_name 을 지원합니다#.tempdb

<drop_clustered_index_option>

적용 대상: SQL Server 2008(10.0.x) 이상 버전인 SQL Database.

클러스터형 인덱스 옵션을 제어합니다. 이러한 옵션은 다른 인덱스 형식과 함께 사용할 수 없습니다.

MAXDOP = max_degree_of_parallelism

적용 대상: SQL Server 2008(10.0.x) 이상 버전, SQL Database(성능 수준 P2 및 P3만 해당).

인덱스 작업 동안 max degree of parallelism 구성 옵션을 재정의합니다. 자세한 내용은 최대 병렬 처리 수준 구성(서버 구성 옵션)을 참조하세요. 병렬 계획 실행에 사용되는 프로세서 수를 제한하는 데 사용합니다 MAXDOP . 최대값은 64개입니다.

max_degree_of_parallelism 다음 값 중 하나일 수 있습니다.

자세한 내용은 병렬 인덱스 작업 구성을 참조하세요.

ONLINE = ON | OFF

적용 대상: SQL Server 2008(10.0.x) 이상 버전인 Azure SQL Database.

인덱스 작업 중 쿼리 및 데이터 수정에 기본 테이블과 관련 인덱스를 사용할 수 있는지 여부를 지정합니다. 기본값은 OFF입니다.

• ON: 장기 테이블 잠금은 유지되지 않습니다. 따라서 기본 테이블에 대한 쿼리나 업데이트를 계속할 수 있습니다.

• OFF: 테이블 잠금이 적용되고 인덱스 작업 중에 테이블을 사용할 수 없습니다.

이 ONLINE 옵션은 클러스터형 인덱스를 삭제할 때만 지정할 수 있습니다. 자세한 내용은 설명 섹션을 참조하세요.

{ partition_scheme_name(column_name)로 이동 | filegroup_name | "default" }

적용 대상: SQL Server 2008(10.0.x) 이상 버전 SQL Database는 파일 그룹 이름으로 지원합니다 "default" .

현재 클러스터형 인덱스의 리프 수준에 있는 데이터 행을 옮길 위치를 지정합니다. 데이터는 힙 형태로 새 위치로 옮겨집니다. 파티션 구성표 또는 파일 그룹을 새 위치로 지정할 수도 있지만 이미 존재하는 파티션 구성표 또는 파일 그룹이어야 합니다. MOVE TO 인덱싱된 뷰 또는 비클러스터형 인덱스에는 유효하지 않습니다. 파티션 구성표 또는 파일 그룹을 지정하지 않으면 결과 테이블은 클러스터형 인덱스용으로 정의된 것과 동일한 파티션 구성표 또는 파일 그룹에 있습니다.

클러스터형 인덱스를 사용하여 MOVE TO삭제하면 기본 테이블의 비클러스터형 인덱스가 다시 작성되지만 원래 파일 그룹 또는 파티션 구성표에 남아 있습니다. 기본 테이블을 다른 파일 그룹 또는 파티션 구성표로 이동하는 경우 비클러스터형 인덱스는 기본 테이블(힙)의 새 위치와 일치하도록 이동되지 않습니다. 따라서 비클러스터형 인덱스가 전에 클러스터형 인덱스에 맞추어 정렬되었다 하더라도 더 이상 힙에 정렬되지는 않습니다. 분할된 인덱스 맞춤에 대한 자세한 내용은 분할된 테이블 및 인덱스를 참조 하세요.

partition_scheme_name ( column_name )

적용 대상: SQL Server 2008(10.0.x) 이상 버전인 SQL Database.

파티션 구성표를 결과 테이블의 위치로 지정합니다. CREATE PARTITION SCHEME 또는 ALTER PARTITION SCHEME을 실행하여 파티션 구성표를 이미 만들어야 합니다. 지정된 위치가 없고 테이블이 분할되어 있다면 해당 테이블은 기존 클러스터형 인덱스와 동일한 파티션에 포함됩니다.

스키마의 열 이름은 인덱스 정의의 열로 제한되지 않습니다. 기본 테이블의 모든 열을 지정할 수 있습니다.

filegroup_name

적용 대상: SQL Server 2008(10.0.x) 이상 버전

파일 그룹을 결과 테이블의 위치로 지정합니다. 위치를 지정하지 않고 테이블이 분할되지 않은 경우 결과 테이블은 클러스터형 인덱스와 동일한 파일 그룹에 포함됩니다. 파일 그룹은 이미 존재해야 합니다.

"default"

결과 테이블의 기본 위치를 지정합니다.

FILESTREAM_ON { partition_scheme_name | filestream_filegroup_name | "default" }

적용 대상: SQL Server 2008(10.0.x) 이상 버전

현재 클러스터형 인덱스의 리프 수준에 있는 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 데이터의 기본 위치를 지정합니다.

설명

비클러스터형 인덱스가 삭제되면 인덱스 정의가 메타데이터에서 제거되고 데이터베이스 파일에서 인덱스 데이터 페이지(B-트리)가 제거됩니다. 클러스터형 인덱스가 삭제되면 인덱스 정의가 메타데이터에서 제거되고 클러스터형 인덱스의 리프 수준에 저장된 데이터 행은 정렬되지 않은 결과 테이블인 힙에 저장됩니다. 인덱스가 이전에 점유하고 있던 모든 공간은 반환됩니다. 반환된 공간은 다른 데이터베이스 개체에서 사용할 수 있습니다.

인덱스가 있는 파일 그룹이 오프라인이거나 읽기 전용으로 설정된 경우 인덱스는 삭제할 수 없습니다.

인덱싱된 뷰의 클러스터형 인덱스가 삭제되면 동일한 보기의 모든 비클러스터형 인덱스 및 자동 생성 통계가 자동으로 삭제됩니다. 수동으로 만든 통계는 삭제되지 않습니다.

구문 <table_or_view_name>.<index_name> 은 이전 버전과의 호환성을 위해 유지 관리됩니다. XML 인덱스 또는 공간 인덱스는 이전 버전과 호환되는 구문을 사용하여 삭제할 수 없습니다.

익스텐트가 128 이상인 인덱스를 삭제하면 데이터베이스 엔진은 트랜잭션이 커밋될 때까지 실제 페이지 할당 취소 및 이와 관련된 잠금을 지연합니다.

인덱스를 다시 구성 또는 다시 작성하기 위해 인덱스를 삭제하고 다시 만드는 경우도 있습니다. 예를 들면 새 채우기 비율 값을 적용하거나 대량 로드 후 데이터를 다시 구성하기 위해서입니다. 이렇게 하려면 특히 클러스터형 인덱스의 경우 ALTER INDEX를 사용하는 것이 더 효율적입니다. ALTER INDEX REBUILD 에는 비클러스터형 인덱스를 다시 빌드하는 오버헤드를 방지하기 위한 최적화가 있습니다.

DROP INDEX와 함께 옵션 사용

클러스터형 인덱스 삭제 시 다음 인덱MAXDOPONLINEMOVE TO스 옵션을 설정할 수 있습니다.

클러스터형 인덱스 삭제 및 결과 테이블을 단일 트랜잭션의 다른 파일 그룹 또는 파티션 구성표로 이동하는 데 사용합니다 MOVE TO .

지정할 ONLINE = ON때 기본 데이터 및 연결된 비클러스터형 인덱스에 대한 쿼리 및 수정은 트랜잭션에 의해 DROP INDEX 차단되지 않습니다. 클러스터형 인덱스는 한 번에 한 개씩만 온라인으로 삭제할 수 있습니다. 옵션에 대한 전체 설명은 ONLINE CREATE INDEX를 참조하세요.

인덱스가 보기에서 비활성화되거나 리프 수준 데이터 행에 텍스트, ntext, image, varchar(max), nvarchar(max), varbinary(max) 또는 xml 열이 포함된 경우 클러스터형 인덱스를 온라인으로 삭제할 수 없습니다.

ONLINE = ON 및 MOVE TO 옵션을 사용하려면 더 많은 임시 디스크 공간이 필요합니다.

인덱스를 삭제하면 결과 힙이 sys.indexes 카탈로그 뷰에 열과 함께 NULL name 표시됩니다. 테이블 이름을 보려면 에 sys.tables 조 sys.indexes 인합니다object_id. 쿼리 예는 예 1을 참조하세요.

SQL Server 2005 Enterprise Edition 이상을 DROP INDEX 실행하는 다중 프로세서 컴퓨터에서는 다른 쿼리와 마찬가지로 클러스터형 인덱스 삭제와 관련된 검색 및 정렬 작업을 수행하기 위해 더 많은 프로세서를 사용할 수 있습니다. 인덱스 옵션을 지정 MAXDOP 하여 문을 실행하는 DROP INDEX 데 사용되는 프로세서 수를 수동으로 구성할 수 있습니다. 자세한 내용은 병렬 인덱스 작업 구성을 참조하세요.

클러스터형 인덱스를 삭제한 경우 파티션 구성표를 수정하지 않으면 해당 힙 파티션에서 데이터 압축 설정이 유지됩니다. 분할 체계가 변경되면 모든 파티션이 압축되지 않은 상태(DATA_COMPRESSION = NONE)로 다시 작성됩니다. 클러스터형 인덱스를 삭제하고 파티션 구성표를 변경하려면 다음 두 단계를 수행해야 합니다.

1. 클러스터형 인덱스를 삭제합니다.

2. 압축 옵션을 지정하는 옵션을 사용하여 ALTER TABLE ... REBUILD ... 테이블을 수정합니다.

클러스터형 인덱스를 삭제 OFFLINE하면 상위 수준의 클러스터형 인덱스만 제거되므로 작업이 빠릅니다. 클러스터형 인덱스가 삭제되면 ONLINESQL Server는 1단계에서 한 번, 2단계에 대해 한 번씩 힙을 두 번 다시 빌드합니다. 데이터 압축에 대한 자세한 내용은 데이터 압축을 참조 하세요.

XML 인덱스

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 고정 데이터베이스 역할에 부여됩니다.

예제

이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.

A. 인덱스 삭제

다음 예제에서는 AdventureWorks2022 데이터베이스에서 테이블의 인덱 IX_ProductVendor_BusinessEntityID ProductVendor 스를 삭제합니다.

DROP INDEX IX_ProductVendor_BusinessEntityID
    ON Purchasing.ProductVendor;
GO

B. 여러 인덱스 삭제

다음 예제에서는 AdventureWorks2022 데이터베이스의 단일 트랜잭션에서 두 개의 인덱스를 삭제합니다.

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 2008(10.0.x) 이상 버전인 SQL Database.

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

D. 클러스터형 인덱스 온라인 삭제 및 새 파일 그룹으로 테이블 이동

다음 예에서는 NewGroup 절을 사용하여 온라인으로 클러스터형 인덱스를 삭제하고 결과 테이블을 MOVE TO 파일 그룹으로 옮깁니다. 테이블을 이동하기 전과 이동한 후에 파일 그룹에서의 인덱스 및 테이블 배치를 확인하기 위해 sys.indexes, sys.tables및 sys.filegroups 카탈로그 뷰를 쿼리합니다. SQL Server 2016(13.x)부터 구문을 사용할 DROP INDEX IF EXISTS 수 있습니다.

적용 대상: SQL Server 2008(10.0.x) 이상 버전

--Create a clustered index on the PRIMARY filegroup if the index does not exist.
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 AdventureWorks2022
        ADD FILEGROUP NewGroup;
    ALTER DATABASE AdventureWorks2022
        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. 제약 조건이 있을 경우 제약 조건을 먼저 제거해야 합니다.

-- 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 인덱스 삭제

다음 예제에서는 AdventureWorks2022 데이터베이스의 ProductModel 테이블에 XML 인덱스를 삭제합니다.

DROP INDEX PXML_ProductModel_CatalogDescription
    ON Production.ProductModel;

G. FILESTREAM 테이블에 클러스터형 인덱스 삭제

다음 예에서는 클러스터형 인덱스를 온라인으로 삭제하고 MyPartitionScheme 절과 MOVE TO 절을 모두 사용하여 테이블(힙) 결과 및 FILESTREAM 데이터를 FILESTREAM ON 파티션 구성표로 이동합니다.

적용 대상: SQL Server 2008(10.0.x) 이상 버전

DROP INDEX PK_MyClusteredIndex
    ON dbo.MyTable
    WITH (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
• sys.tables
• sys.filegroups
• sp_spaceused