인덱스 및 제약 조건 비활성화

적용 대상:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

이 항목에서는 SQL Server Management Studio 또는 Transact-SQL을 사용하여 SQL Server에서 인덱스 또는 제약 조건을 사용하지 않도록 설정하는 방법을 설명합니다. 인덱스를 사용하지 않도록 설정하면 인덱스 및 기본 테이블 데이터에 대한 클러스터형 인덱스에 대한 사용자 액세스를 방지할 수 있습니다. 인덱스 정의는 메타데이터에 남아 있고 인덱스 통계는 비클러스터형 인덱스에 유지됩니다. 뷰의 클러스터형 인덱스 또는 비클러스터형 인덱스를 비활성화하면 인덱스 데이터가 물리적으로 삭제됩니다. 테이블의 클러스터형 인덱스를 비활성화하면 테이블 데이터에 액세스할 수 없습니다. 데이터는 테이블에 계속 남아 있지만 인덱스를 삭제하거나 다시 작성할 때까지 DML(데이터 조작 언어) 작업에 데이터를 사용할 수 없습니다.

항목 내용

시작하기 전에

제한 사항

  • 비활성화된 인덱스는 유지 관리되지 않습니다.

  • 쿼리 최적화 프로그램은 쿼리 실행 계획을 만들 때 비활성화된 인덱스로 간주하지 않습니다. 또한 테이블 힌트를 사용하여 비활성화된 인덱스를 참조하는 쿼리가 실패합니다.

  • 기존 비활성화된 인덱스와 동일한 이름을 사용하는 인덱스는 만들 수 없습니다.

  • 비활성화된 인덱스 삭제할 수 있습니다.

  • 고유 인덱스를 사용하지 않도록 설정하면 PRIMARY KEY 또는 UNIQUE 제약 조건과 다른 테이블에서 인덱싱된 열을 참조하는 모든 FOREIGN KEY 제약 조건도 사용하지 않도록 설정됩니다. 클러스터형 인덱스를 사용하지 않도록 설정하면 기본 테이블에서 들어오고 나가는 모든 FOREIGN KEY 제약 조건도 사용하지 않도록 설정됩니다. 인덱스가 비활성화되면 경고 메시지에 제약 조건 이름이 나열됩니다. 인덱스를 다시 빌드한 후에는 ALTER TABLE CHECK CONSTRAINT 문을 사용하여 모든 제약 조건을 수동으로 사용하도록 설정해야 합니다.

  • 연결된 클러스터형 인덱스를 사용하지 않도록 설정하면 비클러스터형 인덱스가 자동으로 비활성화됩니다. 테이블 또는 뷰의 클러스터형 인덱스가 사용되거나 테이블의 클러스터형 인덱스가 삭제될 때까지 사용하도록 설정할 수 없습니다. ALTER INDEX ALL REBUILD 문을 사용하여 클러스터형 인덱스를 사용하도록 설정하지 않은 경우 비클러스터형 인덱스를 명시적으로 사용하도록 설정해야 합니다.

  • ALTER INDEX ALL REBUILD 문은 테이블의 비활성화된 인덱스를 모두 작성하고 비활성화합니다. 뷰의 비활성화된 인덱스는 예외입니다. 뷰의 인덱스는 별도의 ALTER INDEX ALL REBUILD 문에서 사용하도록 설정해야 합니다.

  • 테이블에서 클러스터형 인덱스를 사용하지 않도록 설정하면 해당 테이블을 참조하는 뷰에서 클러스터형 및 비클러스터형 인덱스가 모두 비활성화됩니다. 이러한 인덱스는 참조되는 테이블의 인덱스에 따라 다시 작성해야 합니다.

  • 클러스터형 인덱스를 삭제하거나 다시 작성하는 것 외에는 비활성화된 클러스터형 인덱스의 데이터 행에 액세스할 수 없습니다.

  • 테이블에 비활성화된 클러스터형 인덱스가 없는 경우 비활성화된 비클러스터형 인덱스가 온라인으로 다시 작성될 수 있습니다. 그러나 ALTER INDEX REBUILD 또는 CREATE INDEX WITH DROP_EXISTING 문을 사용하는 경우 항상 비활성화된 클러스터형 인덱스 오프라인을 다시 빌드해야 합니다. 온라인 인덱스 작업에 대한 자세한 내용은 인덱스 작업 온라인 수행을 참조하세요.

  • 클러스터형 인덱스가 비활성화된 테이블에서 CREATE STATISTICS 문을 성공적으로 실행할 수 없습니다.

  • 인덱스가 비활성화된 상태이고 다음 조건에 해당하면 AUTO_CREATE_STATISTICS 데이터베이스 옵션이 열에 대한 새 통계를 만듭니다.

    • AUTO_CREATE_STATISTICS가 ON으로 설정된 경우

    • 열에 대한 기존 통계가 없는 경우

    • 통계는 쿼리 최적화 중에 필요합니다.

  • 클러스터형 인덱스가 비활성화되면 DBCC CHECKDB 가 기본 테이블에 대한 정보를 반환할 수 없습니다. 대신 이 문은 클러스터형 인덱스가 비활성화되었다고 보고합니다. DBCC INDEXDEFRAG 를 사용하여 비활성화된 인덱스의 조각 모음을 수행할 수 없습니다. 이 문은 실패하고 오류 메시지가 표시됩니다. DBCC DBREINDEX를 사용하여 비활성화된 인덱스를 다시 작성할 수 있습니다.

  • 새 클러스터형 인덱스를 만들면 이전에 사용하지 않도록 설정된 비클러스터형 인덱스를 사용할 수 있습니다. 자세한 내용은 인덱스 및 제약 조건 사용을 참조 하세요.

보안

사용 권한

ALTER INDEX를 실행하려면 최소한 테이블 또는 뷰에 대한 ALTER 권한이 필요합니다.

SQL Server Management Studio 사용

인덱스를 비활성화하려면

  1. 개체 탐색기에서 더하기 기호를 클릭하여 인덱스를 비활성화할 테이블이 포함된 데이터베이스를 확장합니다.

  2. 더하기 기호를 클릭하여 테이블 폴더를 확장합니다 .

  3. 더하기 기호를 클릭하여 인덱스 비활성화하려는 테이블을 확장합니다.

  4. 더하기 기호를 클릭하여 Indexes 폴더를 확장합니다 .

  5. 사용하지 않도록 설정할 인덱스 마우스 오른쪽 단추로 클릭하고 사용 안 함을 선택합니다.

참고 항목

테이블이 디자인 모드에서 열려 있는 경우 사용 안 함 컨트롤을 사용할 수 없습니다. 계속하려면 테이블 디자이너를 닫고 다시 시작합니다.

  1. 인덱스 사용 안 함 대화 상자에서 올바른 인덱스가 인덱스에 있는지 확인하여 그리드를 사용하지 않도록 설정하고 확인을 클릭합니다.

테이블의 모든 인덱스를 사용하지 않도록 설정하려면

  1. 개체 탐색기에서 더하기 기호를 클릭하여 인덱스를 비활성화할 테이블이 포함된 데이터베이스를 확장합니다.

  2. 더하기 기호를 클릭하여 테이블 폴더를 확장합니다 .

  3. 더하기 기호를 클릭하여 인덱스를 사용하지 않도록 설정할 테이블을 확장합니다.

  4. 인덱스 폴더를 마우스 오른쪽 단추로 클릭하고 모두 사용 안 함을 선택합니다.

  5. 인덱스 비활성화 대화 상자에서 올바른 인덱스가 비활성화할 인덱스 표에 있는지 확인한 다음 확인을 클릭합니다. 비활성화할 인덱스 표에서 인덱스를 제거하려면 인덱스를 선택한 다음 Delete 키를 누릅니다.

인덱스 사용 안 함 대화 상자에서 다음 정보를 사용할 수 있습니다.

인덱스 이름
인덱스의 이름을 표시합니다. 실행 중에 이 열에는 상태를 나타내는 아이콘도 표시됩니다.

테이블 이름
인덱스가 만들어진 테이블 또는 뷰의 이름을 표시합니다.

인덱스 유형
인덱스의 유형( 클러스터형, 비클러스터형, 공간또는 XML)을 표시합니다.

상태
사용 안 함 작업의 상태를 표시합니다. 실행 후 가능한 값은 다음과 같습니다.

  • Blank

    실행 전에 상태 가 비어 있습니다.

  • 진행 중

    인덱스 비활성화가 시작되었지만 아직 완료되지 않았습니다.

  • 성공

    사용 안 함 작업이 성공적으로 완료되었습니다.

  • 오류

    인덱스 사용 안 함 작업 중에 오류가 발생했으며 작업이 성공적으로 완료되지 않았습니다.

  • 중지됨

    사용자가 작업을 중지했기 때문에 인덱스 사용 안 함이 성공적으로 완료되지 않았습니다.

메시지
사용 안 함 작업 중에 오류 메시지의 텍스트를 제공합니다. 실행 중에는 오류 텍스트가 하이퍼링크로 표시됩니다. 하이퍼링크의 텍스트는 오류 본문을 설명합니다. 메시지 열은 거의 전체 메시지 텍스트를 읽을 수 있을 만큼 넓지 않습니다. 다음과 같은 두 가지 방법으로 전체 텍스트를 볼 수 있습니다.

  • 메시지 셀 위로 마우스 포인터를 이동하여 오류 텍스트가 있는 도구 설명을 표시합니다.

  • 하이퍼링크를 클릭하여 전체 오류를 표시하는 대화 상자를 표시합니다.

Transact-SQL 사용

인덱스를 비활성화하려면

  1. 개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.

  2. 표준 도구 모음에서 새 쿼리를 클릭합니다.

  3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.

    USE AdventureWorks2022;  
    GO  
    -- disables the IX_Employee_OrganizationLevel_OrganizationNode index  
    -- on the HumanResources.Employee table  
    ALTER INDEX IX_Employee_OrganizationLevel_OrganizationNode ON HumanResources.Employee  
    DISABLE;  
    

테이블의 모든 인덱스를 사용하지 않도록 설정하려면

  1. 개체 탐색기에서 데이터베이스 엔진인스턴스에 연결합니다.

  2. 표준 도구 모음에서 새 쿼리를 클릭합니다.

  3. 다음 예를 복사하여 쿼리 창에 붙여 넣고 실행을 클릭합니다.

    USE AdventureWorks2022;  
    GO  
    -- Disables all indexes on the HumanResources.Employee table.  
    ALTER INDEX ALL ON HumanResources.Employee  
    DISABLE;  
    

자세한 내용은 ALTER INDEX(Transact-SQL)를 참조하세요.