MSSQL Index(인덱스) 생성, 조회, 삭제 방법

MSSQL Index를 생성, 조회, 삭제하는 방법에 대해서 알아보고 소스 코드로 실습해 보겠습니다.

 

Index(인덱스) 생성(create) 방법

클러스터 형 인덱스 생성(Clustered Index).

• Clustered Index는 테이블당 하나의 Clustered Index만 생성할 수 있습니다.
• Clustered Index를 생성하면 테이블의 데이터 행이 해당 Index 키를 기준으로 물리적으로 재배열됩니다.
• 기본 키(Primark Key)를 생성하면 Clustered Index가 자동으로 생성됩니다. 

소스코드.

CREATE TABLE AurumGuide_Index (
    AurumId           INT NOT NULL,
    AurumNm           VARCHAR(255) NOT NULL,
    AurumAge          INT  NULL,
    AurumAddress      VARCHAR(500)  NULL
);
-- Create Sample Data
INSERT INTO dbo.AurumGuide_Index(AurumId,AurumNm) 
VALUES (272, N'Ken')
      ,(273, N'Brian')
      ,(274, N'Stephen')
      ,(275, N'Michael')
      ,(276, N'Linda');
 
/* CLUSTERED Index CREATE */
CREATE CLUSTERED INDEX AurumGuide_Index_01 ON dbo.AurumGuide_Index (AurumId);

 

비 클러스터 형 인덱스 생성(NON CLUSTERED INDEX).

• NON CLUSTERED INDEX는 데이터 행의 주소(RID)를 별도의 인덱스 페이지에 저장합니다. 
• Non-Clustered Index는 테이블의 데이터를 그대로 두고 지정된 칼럼에 대해 정렬시킨 인덱스를 생성합니다. 데이터 페이지를 건드리지 않습니다.
• Non-Clustered Index는 검색 속도는 느리지만, 데이터의 입력, 수정, 삭제는 빠릅니다.
• Non-Clustered Index는 필요한 만큼 index를 생성할 수 있습니다.
• index 생성 시 NON CLUSTERED 키워드는 생략할 수 있습니다.

소스코드.

/* NONCLUSTERED Index CREATE */
CREATE NONCLUSTERED INDEX AurumGuide_Index_02 ON dbo.AurumGuide_Index (AurumId,AurumNm);

 

Index(인덱스) 조회 방법

Index(인덱스) 이름으로 검색하는 방법.

• sys.indexes 이용해서 index 검색하는 방법입니다.

소스코드.

SELECT *
FROM  sys.indexes
WHERE name like 'AurumGuide%'; -- index name

테이블 이름으로 index 검색하는 방법.

• table_name : 인덱스가 속한 테이블의 이름.
• index_name : 인덱스 이름.
• column_name : 인덱스에 사용된 칼럼.
• index_type(type_desc) : 인덱스 유형.

소스코드.

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
JOIN sys.indexes b ON a.object_id = b.object_id
JOIN sys.index_columns c ON b.object_id = c.object_id 
AND b.index_id = c.index_id
JOIN sys.columns d ON c.object_id = d.object_id 
AND c.column_id = d.column_id
WHERE a.name = 'AurumGuide_index'; -- table name

 

Index(인덱스) 삭제, 변경 방법

Index(인덱스) 삭제 방법.

• 사용 중인 index를 삭제할 때는 ONLINE 옵션을 사용하는 걸 추천합니다.
• 대량의 data가 있는 경우 삭제를 하게 되면 lock이 발생하므로 online 옵션이 필요합니다.

소스코드.

 /* DROP Index  */
DROP INDEX [AurumGuide_Index_01] ON [dbo].[AurumGuide_Index] WITH ( ONLINE = OFF );

Index(인덱스) 변경 방법.

• 기본적으로 T-SQL을 사용하여 INDEX 이름을 변경하는 것은 불가능합니다. 
• 물론 삭제하고 다시 생성하는 방법도 있지만 사용 중인 index에 대해서 변경할 때는 ssms 추천합니다.

SSMS 이용해서 Rename 하는 방법.

Index(인덱스) 변경 방법