[MSSQL] 인덱스 생성, 변경 방법 (Create Index)

SQL Server에서 인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용한다. 인덱스를 생성한 후 변경하는 명령은 없으며 인덱스를 삭제 후 다시 생성하는 방법을 사용해야 한다. 인덱스는 클러스터형과 비클러스터형 인덱스가 있으며, 클러스터형 인덱스는 테이블에 하나만 생성이 가능하며, 비클러스터형 인덱스는 여러 개를 생성할 수 있다. 일반적인 인덱스 생성은 비클러스터형 인덱스를 생성한다고 생각하면 된다.

 

MSSQL 인덱스 생성, 삭제 방법

목차 인덱스 생성, 삭제 방법 인덱스 변경 방법 인덱스 조회 클러스터형, 비클러스터형 인덱스

 

인덱스 생성, 삭제 방법

인덱스 생성

CREATE INDEX emp_idx01 ON dbo.emp (hiredate)

 

CREATE INDEX [인덱스명] ON [테이블명] ([칼럼1], [칼럼2], [칼럼3] ...)

인덱스명은 임의로 정하여 생성하면 된다.

 

인덱스 칼럼의 기본 정렬은 오름차순(ASC)이며, 내림차순(DESC)으로 변경하여 생성하고 싶으면 아래를 참고하면 된다.

 

/* 인덱스 컬럼 정렬1 (단일 컬럼) */
CREATE INDEX emp_idx01 ON dbo.emp (hiredate DESC)

/* 인덱스 컬럼 정렬2 (다중 컬럼) */
CREATE INDEX emp_idx01 ON dbo.emp (hiredate DESC, deptno ASC)

 

오름차순(ASC)은 생략해도 오름차순으로 생성된다. 인덱스 정렬은 특별한 경우가 아니면 기본 정렬(ASC)로 생성한다. 그러나 테이블을 조회하여 정렬(ORDER BY)을 자주 내림차순으로 변경해야 하는 케이스인 경우 인덱스를 내림차순으로 생성해 놓으면 정렬(ORDER BY)을 하지 않아도 인덱스가 생성된  칼럼 정렬 기준으로 데이터가 정렬되어 조회된다.

 

인덱스 삭제

DROP INDEX emp_idx01 ON dbo.emp

 

DROP INDEX [인덱스명] ON [테이블명]

 

인덱스 변경 방법

/* 1. 변경할 인덱스 삭제 */
DROP INDEX emp_idx01 ON dbo.emp

/* 2. 변경할 인덱스 재생성 */
CREATE INDEX emp_idx01 ON dbo.emp (hiredate, deptno)

 

인덱스 칼럼을 추가하거나 빼야 하는 경우 인덱스를 변경해야 한다. 그러나 인덱스를 변경하는 명령은 없다.

인덱스를 변경하기 위해서는 생성된 인덱스를 삭제하고 다시 인덱스를 생성해야 한다.

① 인덱스 삭제 → ② 인덱스 재생성 → ③ 인덱스 변경 완료

 

인덱스 조회

SELECT a.name      AS table_name
     , b.name      AS index_name
     , d.name      AS column_name
     , b.type_desc AS index_type
  FROM SYS.TABLES a
     , SYS.INDEXES b
     , SYS.INDEX_COLUMNS c
     , SYS.COLUMNS d
 WHERE a.name = 'emp' 
   AND a.object_id = b.object_id 
   AND b.object_id = c.object_id 
   AND b.index_id  = c.index_id 
   AND c.object_id = d.object_id 
   AND c.column_id = d.column_id

 

 

emp 테이블을 조회하면 emp_idx01 인덱스가 비클러스터형으로 생성된 것을 확인할 수 있다.

 

클러스터형, 비클러스터형 인덱스

/* 클러스터형 인덱스 생성 */
CREATE CLUSTERED INDEX emp_idx02 ON dbo.emp (empno)

/* 비클러스터형 인덱스 생성 */
CREATE NONCLUSTERED INDEX emp_idx03 ON dbo.emp (deptno)
CREATE INDEX emp_idx04 ON dbo.emp (job)

 

클러스터형 인덱스는 물리적으로 행을 재배열하여 저장하며, 테이블당 하나의 클러스터형 인덱스를 생성할 수 있다. 기본 키(PK)를 생성하면 클러스터형 인덱스가 생성된다. 

데이터 접근 방식: 루트 페이지 → 데이터 페이지

 

비클러스터형 인덱스는 데이터 행의 주소(RID)를 별도의 인덱스 페이지에 저장하며, 테이블에 여러 개(버전마다 상의)의 비클러스터형 인덱스를 생성할 수 있다. NONCLUSTERED 키워드는 생략해도 비클러스터형 인덱스가 생성된다.

데이터 접근 방식: 루트 페이지 → 리프 페이지 → 데이터 페이지

 

[MSSQL] 인덱스 힌트 사용법 (Index Hint)

[MSSQL] 테이블 기본키(PK) 추가, 변경 방법