17. [MS-SQL] 인덱스가 걸려있는 칼럼 조회하기

어떤 테이블의 어떤 칼럼에 인덱스가 걸려있는지 조회하기 위한 쿼리 입니다.

SELECT
    -- 테이블 이름
    TableName = T.name,
    -- 인덱스 이름
    IndexName = IND.name,
    -- 인덱스 ID
    IndexId = IND.index_id,
    -- 인덱스 컬럼 ID
    ColumnId = IC.index_column_id,
    -- 컬럼 이름
    ColumnName = COL.name,
    -- 인덱스 관련 모든 정보
    IND.*,
    -- 인덱스 컬럼 관련 모든 정보
    IC.*,
    -- 컬럼 관련 모든 정보
    COL.*
FROM
    -- 인덱스 정보가 있는 시스템 뷰
    sys.indexes IND
INNER JOIN
    -- 인덱스 컬럼 정보가 있는 시스템 뷰
    sys.index_columns IC ON IND.object_id = IC.object_id AND IND.index_id = IC.index_id
INNER JOIN
    -- 컬럼 정보가 있는 시스템 뷰
    sys.columns COL ON IC.object_id = COL.object_id AND IC.column_id = COL.column_id
INNER JOIN
    -- 테이블 정보가 있는 시스템 뷰
    sys.tables T ON IND.object_id = T.object_id
WHERE
    -- 기본 키가 아닌 인덱스만 선택 (is_primary_key = 0)
    -- is_primary_key: 1 = 기본 키 인덱스
    IND.is_primary_key = 0
    -- 유니크 인덱스가 아닌 것만 선택 (is_unique = 0)
    -- is_unique: 1 = 유니크 인덱스
    AND IND.is_unique = 0
    -- 유니크 제약 조건이 아닌 것만 선택 (is_unique_constraint = 0)
    -- is_unique_constraint: 1 = 유니크 제약 조건
    AND IND.is_unique_constraint = 0
    -- 논 클러스터드 인덱스만 선택 (type = 2)
    -- type: 1 = 클러스터드 인덱스, 2 = 논 클러스터드 인덱스, 3 = XML 인덱스, 4 = 공간 인덱스, 5 = 클러스터드 컬럼스토어 인덱스, 6 = 논 클러스터드 컬럼스토어 인덱스
    AND IND.type = 2
    -- 시스템이 생성한 테이블이 아닌 사용자 정의 테이블만 선택 (is_ms_shipped = 0)
    -- is_ms_shipped: 1 = 시스템이 생성한 테이블
    AND T.is_ms_shipped = 0
ORDER BY
    -- 테이블 이름, 인덱스 이름, 인덱스 ID, 인덱스 컬럼 ID 순으로 정렬
    T.name, IND.name, IND.index_id, IC.index_column_id;

 

Where절 조건문 설명

 

• IND.is_primary_key
  • 0: 기본 키가 아닌 인덱스만 선택합니다.
  • 1: 기본 키 인덱스
• IND.is_unique
  • 0: 유니크 인덱스가 아닌 것만 선택합니다.
  • 1: 유니크 인덱스
• IND.is_unique_constraint
  • 0: 유니크 제약 조건이 아닌 것만 선택합니다.
  • 1: 유니크 제약 조건
• IND.type
  
  • type: 1 = 클러스터드 인덱스
  • type: 2 = 논 클러스터드 인덱스
  • type: 3 = XML 인덱스
  • type: 4 = 공간 인덱스
  • type: 5 = 클러스터드 컬럼스토어 인덱스
  • type: 6 = 논 클러스터드 컬럼스토어 인덱스
• T.is_ms_shipped
  • 0: 시스템이 생성한 테이블이 아닌 사용자 정의 테이블만 선택합니다.
  • 1: 시스템이 생성한 테이블

---

조회 예시.

 

• TableName: 테이블 이름
• IndexName: 인덱스 이름
• IndexId: 인덱스 ID
• ColumnId: 인덱스 컬럼 ID
• ColumnName: 컬럼 이름
• object_id: 오브젝트 ID
• index_id: 인덱스 ID (위와 동일)
• name: 인덱스 이름 (위와 동일)
• type: 인덱스 유형 (1 = 클러스터드 인덱스, 2 = 논 클러스터드 인덱스 등)
• type_desc: 인덱스 유형 설명 (NONCLUSTERED 등)
• index_column_id: 인덱스 컬럼 ID (위와 동일)
• key_ordinal: 인덱스 키 순서
• partition_ordinal: 파티션 순서
• column_id: 컬럼 ID (위와 동일)
• name: 컬럼 이름 (위와 동일)
• object_id: 오브젝트 ID (위와 동일)

---

특정 쿼리에서 어떤 인덱스가 사용되고 있는지 확인하는 방법.

단축키: Ctl + 'L'

실행 계획 조회