[MSSQL] 기본키 (PRIMARY KEY), 인덱스(INDEX), 클러스터(CLUSTERED)

📌 mssql 테이블 primary 기본 옵션 

 

- 기본옵션을 서술하지 않는다면 

 PRIMARY KEY CLUSTERED
(
 [REG_NO] ASC
)ON [PRIMARY]
) ON [PRIMARY]

 

- 기본옵션 예시 

PRIMARY KEY CLUSTERED
(
 [REG_NO] ASC -- asc : 오름차순 --> 작은 값부터 큰 값 쪽으로의 순서 ex)1, 2, 3, 4, n, n+1..
)WITH (PAD_INDEX  = OFF, 
       STATISTICS_NORECOMPUTE  = OFF, 
       IGNORE_DUP_KEY = OFF, 
       ALLOW_ROW_LOCKS  = ON, 
       ALLOW_PAGE_LOCKS  = ON) 
       ON [PRIMARY]
) ON [PRIMARY]

 

1. PAD_INDEX      : 인덱스 패딩을 지정, 기본값은 OFF 2. STATISTICS_NORECOMPUTE     : 배포 통계를 다시 계산할 지 여부를 지정, 기본 값은 OFF 3. IGNORE_DUP_KEY     : 삽입작업에서 고유 인덱스에 중복된 키 값을 삽입하는 경우에 대한 오류 응답을 지정        인덱스를 만들거나 다시 작성한 후 삽입작업에만 적용        CREATE INDEX, ALTER INDEX 또는 UPDATE를 실행하는 경우, 이 옵션 영향 없음        기본값은 OFF 4. ALLOW_ROW_LOCKS      : 행 잠금 허용 여부를 지정, 기본 값은 OFF 5. ALLOW_PAGE_LOCKS     : 페이지 잠금 허용 여부를 지정 , 기본 값은 OFF

 

 

📌Primary Key, Unique Key, Clustered Index, NonClustered Index의 차이

 

- 테이블을 생성할 때 제목에 언급한 4가지 설정이 헷갈리게 되는 경우가많다.

- Key 와 Index로 나누어 생각하면 된다.

- index는 물리적인 관점의 데이터 저장에 대한 설정이고
- key 는 논리적인 관점의 데이터 저장에 대한 설정이다. (foreign key도 마찬가지)

- 데이터가 입력될 때 생성되는 index를 위주로 data를 정렬할 것인가

  아니면 그냥 data정렬은 하지 않고 index만 추가할 것인가가

  바로 Clustered Index와 NonClustered Index의 차이이다.

- Primary Key는 논리적으로 데이터를 고유하게 식별할 수 있도록 제약조건을 걸어두는 것이다.

 

- Primary Key의 제약조건은 다음과 같다.

Not Null 중복된 값은 허용하지 않음 하나의 table에는 한개의 Primary Key를 허용

 

- Unique Key의 제약조건은 다음과 같다.

Null 값도 입력가능 (단 1개만 가능) 중복된 값은 허용하지 않음 하나의 table에 여러개의 unique key를 허용

- 사람을 헷갈리게 만드는 주 요인은

  바로 Primary Key의 Default 설정이 Clustered Index라는 부분에 있다.

- 테이블을 만들고 Primary Key를 설정한 후

  시스템 테이블 생성 쿼리로 해당 테이블을 확인해보면

  다음과 같은 설정으로 잡혀있는 것을 확인할 수 있다.

CREATE TABLE [dbo].[test2](
	[intCol] [int] IDENTITY(1,1) NOT NULL,
	[varcharCol] [varchar](4) NULL,
	[nvarcharCol] [nvarchar](4) NULL,
	[textCol] [text] NULL,
	[datetimeCol] [datetime] NULL,
PRIMARY KEY CLUSTERED 
(
	[intCol] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

- 위의 내용을 보면 Primary Key를 잡은 테이블에 대해 Clustered 옵션을 통해 Clustered Index를 자동 적용하였다.

- 반드시 Primary Key에 index설정을 포함해야 한다는 제약사항은 없다.

 

- 하지만 보통은 두가지가 동시 적용이 되는게 일반적이긴 하다.

1. Index에 대해서는 데이터의 중복 제약조건은 없다.

• 중복된 값이 있어도 상관없다. (여러개의 중복값이 존재하면 입력된 순서대로 index에 기록이 된다.)

2. 테이블을 디자인 할 때, 다음 두 사항를 구분하여 염두에 두고 디자인을 하면 된다.

• 물리적인 테이블 구조에 대해 생각할 때에는 검색제한자가 되는 컬럼을 index 대상으로 삼고, 컬럼의 밀집도와 컬럼의 변경이 잦은정도에 대해 고려하여 index를 선언한다.
• 논리적인 테이블 구조에 대해 생각할 때에는 Primary Key보다는 Unique Key 요소가 없는지 생각하여 적용하고 그 중 기준이 되는 Key를 Primary Key로 선언한다.

 

출처: https://luvstudy.tistory.com/30

[MSSQL][basic] Primary Key, Unique Key, Clustered Index, NonClustered Index의 차이

 

 

 

📌MSSQL CLUSTERED INDEX와 NONCLUSTERED INDEX 차이점

 

- 테이블을 생성시 인덱스를 생성하거나 추가하는 경우가 있다.

 

- 인덱스를 생성하기 위해서는 CREATE INDEX 문을 사용한다. 

 

- CREATE INDEX문으로 추가시에는 CLUSERED 또는 NONCLUSTERED를 설정할 수 있다. 

CREATE INDEX 인덱스명 ON 스키마.테이블명 (컬럼명) 
-- 인덱스 생성 

DROP INDEX 인덱스명 ON 스키마.테이블명 
-- 인덱스 삭제

 

- 인덱스를 생성한 후 변경하는 명령은 없으며, 삭제 후 다시 생성하는 방법을 사용하여야 한다. 

 

- 인덱스는 클러스터형과 비클러스터형이 있다.

 

	CLUSTERED	NONCLUSTERED
인덱스	테이블당 1개만 가능	테이블에 여러개 가능
정렬	물리적으로 행을 정렬	물리적으로 행을 정렬하지 않음

 

- CLUSTERED INDEX 는 데이터 등록 시 지정한 인덱스 키로 다시 정렬하여 값을 저장한다. 

 

- NONCLUSTERED INDEX 는 데이터를 등록 시 지정한 인덱스로 다시 정렬하지 않는다.

 

- 클러스터인덱스는 책에서 앞부분에 있는 목차처럼 어떤 내용이 있는 지 순서대로 정렬되어 있는 것이다.

 

- 넌클러스터인덱스는 책에서 가장 뒷 부분인 찾기 목록처럼 원하는 카테고리 별 내용을 찾을 수 있도록 되어 있는 것이다.