Transcript DBMS-09장(A)

09: 고급 T-SQL
09
학습 목표
Chapter
고급 T-SQL
테이블 생성과 변경, 삭제 방법을 이해한다.
테이블을 만들고 관리하는 방법을 이해한다.
인덱스의 개념을 파악하고, 구조와 작동 메커니즘을 이해한다.
인덱스를 만들 때의 지침과 실제 설정 방법을 이해한다.
뷰의 정보를 확인하는 방법을 이해한다.
뷰를 이용한 데이터의 수정, 인덱싱된 뷰, 분할된 뷰 등을 이해한다.
저장 프로시저의 처리 메커니즘을 이해하고, 작성 및 호출 방법을
익힌다.
입·출력 매개변수 및 매개변수 생략을 이해한다.
템플릿을 이용한 저장 프로시저 작성 방법을 익힌다.
사용자 정의 함수의 개념을 이해하고, 작성 예를 익힌다.
스칼라 함수의 작성 및 호출 방법을 익힌다.
09
Chapter
고급 T-SQL
1. 테이블 생성 및 관리
2. 인덱스
3. 뷰(고급)
4. 저장 프로시저(고급)
5. 사용자 정의 함수
09
Chapter
1. 테이블 생성 및 관리
테이블 생성
 데이터가 저장되는 구조체 중 테이블이 가장 중요하다. 따라서 테이
블을 만들거나 그 구조를 부분적으로 바꾸거나 삭제하는 것은 데이터
자체를 관리하는 것에 선행되어야 한다.
 한 테이블에는 클러스터형 인덱스가 최대 하나만 만들어질 수 있다.
09
Chapter
1. 테이블 생성 및 관리
테이블 변경
 ALTER TABLE 문을 이용하여 테이블 구조를 변경하는 방법에 대해
알아볼 것이다.
 데이터베이스 운영을 시작한 이후에도 데이터베이스는 지속적으로
수정되는 것이 일반적이므로, 데이터베이스를 정상적이고 효율적으로
운영하려면 이 절의 내용을 잘 알아둘 필요가 있다.
 데이터베이스를 구축하는 동안에는 ALTER TABLE 문이 거의 필요
없지만, 데이터베이스 운영을 시작한 이후에는 반드시 필요하다.
09
Chapter
1. 테이블 생성 및 관리
열 추가
 테이블에 열을 추가할 때 유의할 사항
• 추가되는 열을 기존의 열 사이에 삽입하는 방법은 없으며, 항상 기존의
열 끝에 추가된다. 여기서 NULL / NOT NULL 열들의 순서가 뒤섞이는
문제가 발생하지만 불가피하다.
• NOT NULL이면서 기본값도 없는 열은 추가할 수 없다. 왜냐하면 기존
에 존재하는 행들의 경우, 추가되는 열에도 반드시 데이터가 들어가야
하는데 기본값이 없는 NOT NULL 열에는 임의로 데이터를 넣을 수
없기 때문이다. 따라서 추가되는 열은 NULL로 설정하거나 기본값을
정의해야 한다.
09
Chapter
1. 테이블 생성 및 관리
열 추가
 테이블에 열을 추가하기 위한 구문
• column_name : 추가할 열 이름이다.
• type_schema_name : 데이터 형식이 속한 스키마 이름이다.
• type_name : 데이터 형식이다.
09
Chapter
1. 테이블 생성 및 관리
열 추가
• DEFAULT constant_expression : 제약 조건 기본값을 설정하는
것이다.
• IDENTITY [ ( seed, increment ) ] : IDENTITY 속성을 설정하는
것이다.
• < column_constraint > : 그 밖의 각종 제약 조건을 설정하는 것이다
(자세한 내용은 8장의 무결성과 제약조건을 참고하기 바란다).
09
Chapter
1. 테이블 생성 및 관리
열 추가
예제 2 [예제 8-59]에서 만들었던 test1 테이블에 열을 추가해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
USE Test1DB;
IF OBJECT_ID('test1', 'U') IS NOT NULL
DROP TABLE test1;
GO
CREATE TABLE test1 (
id
int
IDENTITY
, jumin char(14) NOT NULL
, zip
char(7) NOT NULL
CHECK (zip LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]')
);
INSERT test1 (jumin, zip) VALUES ('900101-1234567', '112=119');
INSERT test1 (jumin, zip) VALUES ('900101-1234567', '112-119');
GO
ALTER TABLE test1
ADD
address varchar(50) NOT NULL;
GO
ALTER TABLE test1
ADD
address varchar(50) NOT NULL
DEFAULT '**주소 미입력**';
GO
SELECT * FROM test1;
09
Chapter
열 추가
1. 테이블 생성 및 관리
09
Chapter
1. 테이블 생성 및 관리
열 수정
 열 수정은 열 추가보다 좀 더 까다롭다.
 테이블의 열을 수정할 때 유의할 사항
• 기본값, 체크 등의 제약 조건이나 개체 기본값, 규칙 또는 인덱스가 설정
된 열을 수정하려면 먼저 제약 조건, 인덱스 등은 삭제하고 개체 기본값
이나 규칙은 언바인딩해야 한다.
• 데이터 형식을 수정할 경우, 기존의 것과 호환성이 있는 것으로 수정해야
한다. 예를 들면 smallint에서 int로, char에서 varchar로 수정해야
한다.
09
Chapter
1. 테이블 생성 및 관리
열 수정
• 기존의 열에 들어 있던 가장 큰 숫자 값보다 더 작은 숫자 데이터 형식으로
수정할 수는 없다. 산술 오버플로 오류로 인해 수정에 실패하기 때문이다.
• 기존의 열에 들어 있던 가장 긴 문자열보다 길이가 더 짧은 문자 데이터
형식으로 수정할 수는 없다. 문자열이 잘리는 문제가 생겨 수정에 실패
하기 때문이다.
09
Chapter
1. 테이블 생성 및 관리
열 수정
 테이블의 열을 수정하기 위한 구문
• column_name : 수정할 열의 이름이다.
• type_schema_name : 데이터 형식이 속한 스키마 이름이다.
• new_data_type(precision과 scale 포함) : 수정할 데이터 형식이다
(생략 가능).
• NULL / NOT NULL도 바꿀 수 있다.
09
Chapter
1. 테이블 생성 및 관리
열 수정
예제
 3 테이블의 열을 수정해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
USE Test1DB;
EXEC SP_HELP test1;
ALTER TABLE test1
ALTER COLUMN zip
varchar(4) NULL;
GO
ALTER TABLE test1
DROP CONSTRAINT CK__test1__zip__5FB337D6;
ALTER TABLE test1
ALTER COLUMN zip
varchar(4) NULL;
GO
ALTER TABLE test1
ALTER COLUMN zip
varchar(10) NULL;
EXEC SP_HELP test1;
09
Chapter
열 수정
1. 테이블 생성 및 관리
09
Chapter
열 수정
1. 테이블 생성 및 관리
09
Chapter
1. 테이블 생성 및 관리
열 수정
예제
 4 열의 데이터 형식과 NULL을 수정해보자.
1 USE Test1DB;
2 ALTER TABLE test1
3
ALTER COLUMN jumin
4
varchar(14) NULL;
5 EXEC SP_HELP test1;
09
Chapter
1. 테이블 생성 및 관리
열 수정
 [예제 2]에서 test1 테이블을 새로 만들면서 jumin 열에 [예제 8-63]
처럼 rul_jumin을 바인딩하지 않았었는데, 만약 rul_jumin이 바인딩
되어 있었다면 [예제 4]를 실행하기 전에 다음 명령문을 통해 언바인딩
을 해주어야 한다.
EXEC sp_unbindrule 'test1.jumin‘;
09
Chapter
1. 테이블 생성 및 관리
열 삭제
 열 삭제는 데이터의 삭제를 수반하므로 열 추가나 열 수정의 경우보다
더 주의해야 한다.
 테이블의 열을 삭제할 때 유의할 사항
• 기본값, 체크 등의 제약 조건이나 개체 기본값, 규칙 또는 인덱스가 설정
된 열을 삭제하려면 먼저 제약 조건, 인덱스 등은 삭제하고 개체 기본값
이나 규칙은 언바인딩해야 한다.
• 열 삭제는 영구적으로 적용되고 복구할 수 없으므로, 반드시 데이터
베이스를 백업한 후에 수행해야 한다.
09
Chapter
1. 테이블 생성 및 관리
열 삭제
 테이블의 열을 삭제하기 위한 구문
• column_name : 삭제할 열의 이름으로, 하나 이상 지정할 수 있다.
09
Chapter
1. 테이블 생성 및 관리
열 삭제
예제
 5 열을 삭제해보자.
1
2
3
4
5
6
USE Test1DB;
ALTER TABLE test1
DROP CONSTRAINT DF__test1__address__60A75C0F;
ALTER TABLE test1
DROP COLUMN address;
SELECT * FROM test1;
09
Chapter
1. 테이블 생성 및 관리
테이블 삭제
 먼저 테이블 데이터를 삭제하는 방법을 살펴본 후 테이블을 삭제하는
방법을 알아보자.
 테이블의 데이터를 모두 삭제하는 구문에는 다음의 두 가지가 있다.
• 모든 행을 삭제하는 경우이므로, DELETE 문에서 WHERE 절은 사용
하지 않는다.
• DELETE 문은 각 행의 삭제를 일일이 트랜잭션 로그에 기록한다.
09
Chapter
1. 테이블 생성 및 관리
테이블 삭제
• TRUNCATE 문은 데이터 페이지의 할당 취소만 트랜잭션 로그에 기록
하므로 더 빠르다. 따라서 각각의 행을 복구할 수는 없다.
• DELETE 문은 IDENTITY 값을 그대로 유지하지만, TRUNCATE 문은
초기 값으로 재설정한다. 즉, 다음에 한 행을 삽입할 때 IDENTITY 속성
의 초기 값이 사용된다.
• 참조하는 자식 테이블을 가진 부모 테이블에 대해서는 TRUNCATE 문을
사용할 수 없다. 반면, DELETE 문은 자식 테이블에 부모 테이블의 기본
키를 참조하는 행이 있는지에 따라 삭제 성공 여부가 결정된다.
• DELETE 문과 TRUNCATE 문은 데이터만 삭제하고, 테이블의 구조나
제약 조건, 인덱스 등은 그대로 유지한다.
09
Chapter
1. 테이블 생성 및 관리
테이블 삭제
예제
 6 TableDft와 Temp1 테이블들의 모든 행들을 삭제해보자.
1
2
3
4
5
USE Test1DB;
DELETE TableDft;
TRUNCATE TABLE Temp1;
SELECT * FROM TableDft;
SELECT * FROM Temp1;
09
Chapter
1. 테이블 생성 및 관리
테이블 삭제
 테이블을 삭제하는 구문
• 참조하는 자식 테이블을 가진 부모 테이블은 삭제할 수 없다(부모 테이
블을 삭제하려면 먼저 자식 테이블들을 모두 삭제해야 함).
• 테이블 내의 열에 설정된 각종 제약 조건은 자동으로 모두 삭제되고,
바인딩된 개체들(기본값 또는 규칙)은 모두 언바인딩된다. 따라서 테이
블을 삭제하기 전에 제약 조건 삭제나 개체 언바인딩을 수행할 필요는
없다.
09
Chapter
1. 테이블 생성 및 관리
테이블 삭제
예제
 7 테이블 TableDft와 Temp1을 삭제해보자.
1 USE Test1DB;
2 DROP TABLE TableDft, Temp1;
09
Chapter
2. 인덱스
개요
 테이블 스캔(table scan): 인덱스가 없는 테이블에 행을 삽입하면
특별한 순서 없이 데이터 페이지에 저장된다. 이 상태에서 특정 열 값
을 검색하면 모든 행을 뒤져서 일치하는 열 값을 찾아내는데 이를
테이블 스캔이라고 한다.
 하지만 데이터가 많아지면 테이블 스캔으로 검색하는데 너무 많은
시간이 걸린다.
09
Chapter
2. 인덱스
개요
 인덱스는 책 뒤에 있는 찾아보기(index) 개념과 비슷하다. 찾아보기
의 용어는 알파벳 또는 가나다순으로 정렬되어 있어 쉽게 찾아낼 수
있고, 쪽 번호가 같이 기재되어 있기 때문에 해당 쪽으로 쉽게 이동할
수 있다.
 인덱스는 키가 트리 구조로 정렬되어 있고, 리프 수준(leaf level)의
인덱스 페이지에는 데이터가 위치하는 곳을 가리키는 포인터들이 붙어
있어 빠르게 데이터를 찾아낼 수 있다.
 또한 고유 인덱스로 만들면 유일성 제약 조건도 강화할 수 있다는 장점
이 있다.
09
Chapter
2. 인덱스
개요
 그러나 인덱스 자체가 추가적인 공간을 차지하고, 인덱스를 유지 관리
하는 데 추가적인 시간이 소비된다는 단점도 있다.
 인덱스를 이용하여 데이터를 검색할 때는 시간이 줄어들지만, 데이터
를 추가하고 수정할 때는 인덱스 때문에 시간이 더 걸린다. 따라서
모든 열에 무조건 인덱스를 만들어서는 안 된다.
09
Chapter
2. 인덱스
인덱스 검색 알고리즘
 인덱스를 검색하는 알고리즘은 다음과 같다.
• 루트 인덱스 페이지에서 시작하여 검색하려는 값과 인덱스의 키 값을
차례로 비교한 후 다음과 같이 처리한다.
1. 현재 키가 인덱스 페이지의 마지막 키고, 검색 값이 이 키 값보다 크거나
같으면 이 키의 링크를 따라 이동한다.
2. 검색 값이 현재의 키 값과 같으면 현재 키의 링크를 따라 이동한다.
09
Chapter
2. 인덱스
인덱스 검색 알고리즘
3. 검색 값이 현재의 키 값보다 작으면 이전 키의 링크를 따라 이동한다.
이때 리프 수준에서는 이전 키의 값이 검색 값과 일치해야 하며, 그렇지
않을 때는 검색에 실패한다.
4. 검색 값이 현재의 키 값보다 크면 다음 키로 넘어가 비교 및 처리를 반복
한다.
• 링크를 따라 아래 수준의 인덱스 페이지를 계속 따라 내려가서 리프 수준
에서 링크를 따라 이동할 때까지 1~2를 반복한다.
예제 8
인덱스 검색 예를 살펴보자.
09
Chapter
2. 인덱스
인덱스 검색 알고리즘
09
Chapter
2. 인덱스
페이지 분할
 인덱스 페이지가 꽉 찬 상태에서 키를 삽입하거나 길이가 더 긴 키로
갱신하면 해당 페이지가 분할된다.
 [그림 9-2]를 보자.
09
Chapter
2. 인덱스
페이지 분할
 (a)의 P13에 I라는 키를 삽입해야 하는데 이 페이지가 이미 꽉 차 있
다면 (b)처럼 페이지들이 분할된다.
 기존의 페이지 P24에 약 절반의 키들이 남고 새로 분할된 페이지 P25
에 새로 삽입된 키와 나머지 절반의 키들이 옮겨간다. 이때 상위 수준
의 인덱스 페이지가 추가로 분할될 수도 있다.
 (a)의 루트 페이지 P11이 꽉 찬 상태에서 하위 수준의 페이지가 하나
늘었기 때문에 (b)의 P21과 P22로 페이지들이 분할된다(여기서는
새로운 수준이 추가된다).
09
Chapter
2. 인덱스
페이지 분할
 데이터 페이지의 경우, 클러스터형 인덱스가 설정되면 인덱스 페이지
의 경우와 비슷한 페이지 분할이 발생한다. 그러나 인덱스가 없는
힙 상의 데이터 페이지는 분할되지 않고, 포워딩 포인터(forwarding
pointer)라는 메커니즘을 통해 꽉 찬 페이지 문제를 해결한다.
 인덱스를 만들 때 페이지들을 꽉 채워 놓으면 페이지 분할이 자주 발생
하게 되어 비효율적이므로, 채우기 비율(fill factor)을 설정하여 이를
조절한다.
09
Chapter
2. 인덱스
힙 상의 비클러스터형 인덱스
 힙(heap): 데이터 행들이 특정 순서로 저장되지 않고 데이터 페이지들
사이에도 특별한 순서가 없는 테이블 공간
 [그림9-3]은 힙 상의 비클러스터형 인덱스의 구조와 작동 메커니즘을
보여준다.
09
Chapter
2. 인덱스
힙 상의 비클러스터형 인덱스
09
Chapter
2. 인덱스
힙 상의 비클러스터형 인덱스
 [그림 9-3]은 테이블(이름을 member라고 하자)에 클러스터형 인덱
스가 없는 상태에서 lastname 열에 대해 비클러스터형 인덱스를
만든 경우다. 이때 데이터 행들은 순서 없이 힙에 존재하는 상태다.
 중간 수준의 인덱스 페이지들은 하위 수준의 인덱스 페이지에 대한
포인터를 가지고 있다. 그러나 리프 수준의 인덱스 페이지들은 파일
식별자, 데이터 페이지 번호, 행 번호를 포함하는 행 식별자(Row ID)
를 가지고 있다. 예를 들어, 인덱스 페이지 61의 Matey 키의 행 식별
자 4:706:04는 파일 4의 데이터 페이지 706의 행번호 04를 나타낸다.
09
Chapter
2. 인덱스
힙 상의 비클러스터형 인덱스
 다음과 같은 SELECT 문을 실행할 때의 검색 순서를 알아보자.
SELECT *
FROM member
WHERE lastname = 'Matey';
1. 루트 페이지인 페이지 12에서 검색을 시작한다.
2. 마지막 키인 Martin의 포인터를 따라 페이지 28로 간다.
3. Martin의 포인터를 따라 페이지 61로 간다.
4. Matey 키의 행 식별자에 의해 해당 데이터 페이지인 페이지 706의
행 04로 간다.
5. 이 행의 모든 열 값을 추출한 후 결과 집합을 돌려준다.
09
Chapter
2. 인덱스
클러스터형 인덱스
 인덱스 구조 중 두 번째는 클러스터형 인덱스(clustered index)다.
클러스터형 인덱스에서는 데이터 페이지의 행들이 키에 의해 정렬
되어 있고, 이 데이터 페이지들이 인덱스의 리프 수준을 겸한다.
 [그림9-4]는 클러스터형 인덱스의 구조와 작동 메커니즘을 보여준다.
09
Chapter
2. 인덱스
클러스터형 인덱스
09
Chapter
2. 인덱스
클러스터형 인덱스
 [그림 9-4]는 lastname 열에 대해 클러스터형 인덱스를 만든 경우
다. 데이터 페이지의 행들이 lastname 키에 의해 정렬되어 있고, 이
데이터 페이지들이 인덱스의 리프 수준을 겸하는 것을 알 수 있다.
 다음과 같은 SELECT 문을 실행할 때 검색 순서를 알아보자.
SELECT *
FROM member
WHERE lastname = 'Ota’
09
Chapter
2. 인덱스
클러스터형 인덱스
 클러스터형 인덱스의 리프 수준(데이터 페이지)에는 별도의 포인터가
없이 데이터 행 자체가 저장되어 있으므로 인덱스가 한 수준 줄어든
효과가 있다.
 따라서 인덱스 검색은 더 빠르지만, 데이터 행들을 계속 정렬해야 하는
부담이 있다. 그러므로 클러스터형 인덱스가 꼭 필요하지 않은 경우에
는 비클러스터형 인덱스로 만드는 것이 효율적이다.
09
Chapter
2. 인덱스
클러스터형 인덱스 상의 비클러스터형 인덱스
 인덱스 구조 중 세 번째는 클러스터형 인덱스 상의 비클러스터형
인덱스다.
 테이블에 이미 클러스터형 인덱스가 있는 상태에서 비클러스터형
인덱스를 만들면, 비클러스터형 인덱스의 리프 수준의 페이지는 행
식별자 대신에 클러스터형 인덱스의 키 값을 가지게 된다. 또한 비클러
스터형 인덱스를 이용해서 검색할 때 항상 클러스터형 인덱스까지
이중으로 검색한다.
 [그림 9-5]는 클러스터형 인덱스의 구조와 작동 메커니즘을 보여준다.
09
Chapter
2. 인덱스
클러스터형 인덱스 상의 비클러스터형 인덱스
09
Chapter
2. 인덱스
클러스터형 인덱스 상의 비클러스터형 인덱스
 [그림9-5]는 lastname 열로 먼저 클러스터형 인덱스를 만든 후에
firstname 열로 비클러스터형 인덱스를 만든 경우다. 비클러스터형
인덱스의 리프 수준의 페이지에는 행 식별자 대신에 클러스터형 인덱
스의 키 값이 들어 있다.
- 예를 들어, Mike 키는 행 식별자 대신에 클러스터형 인덱스의 키 값
Nash를 가지고 있다.
09
Chapter
2. 인덱스
클러스터형 인덱스 상의 비클러스터형 인덱스
 다음과 같은 SELECT 문을 실행할 때 검색 순서를 알아보자.
SELECT *
FROM member
WHERE firstname = 'Mike’
1. 비클러스터형 인덱스의 루트 페이지에서 첫 번째 검색을 시작한다.
2. 마지막 키인 Jose의 포인터를 따라 아래 페이지로 간다.
3. 다시 Jose 키의 포인터를 따라 아래 페이지로 간다.
4. 검색 값과 차례로 비교하여 Mike 키를 찾는다.
5. Mike 키가 가지고 있는 클러스터형 인덱스의 키 값 Nash를 클러스
터형 인덱스로 넘긴다.
09
Chapter
2. 인덱스
클러스터형 인덱스 상의 비클러스터형 인덱스
6. 클러스터형 인덱스의 루트 페이지에서 두 번째 검색을 시작한다. 이때
검색하는 값은 비클러스터형 인덱스로부터 넘겨받은 Nash가 된다.
7. Nagata의 포인터를 따라 단말(데이터) 페이지로 간다.
8. 검색 값과 키 값이 일치하는 Nash 행을 찾는다.
9. 이 행의 모든 열 값을 추출한 후 결과 집합을 돌려준다.
09
Chapter
2. 인덱스
클러스터형 인덱스 상의 비클러스터형 인덱스
 이렇게 복잡하게 이중으로 검색해야 하는 이유는 클러스터형 인덱스의
단말 페이지 분할에 있다.
• 만약 비클러스터형 인덱스의 리프 수준의 페이지에 행 식별자가 들어
있다면, 클러스터형 인덱스의 단말 페이지가 분할될 때마다 비클러스
터형 인덱스 페이지에서 많은 행 식별자를 수정해야 할 것이며, 이는
극히 비효율적이다.
• 따라서 클러스터형 인덱스의 단말 페이지가 분할되더라도 비클러스터
형 인덱스를 수정하지 않도록 하기 위해 이런 구조로 설계하는 것이다.
09
Chapter
2. 인덱스
인덱스 작성 지침과 설정
 효율적인 인덱스를 작성하기 위한 지침
• 저장될 데이터를 어떻게 운영할 것인지에 대한 분석을 선행해야 한다.
예를 들어, 예상되는 질의의 종류와 빈도 등이다. 이를 바탕으로 인덱스
의 필요성과 형태가 결정된다.
09
Chapter
2. 인덱스
인덱스 작성 지침과 설정
• 다음과 같은 열에는 인덱스를 설정해야 한다.
- 기본 키(자동적으로 설정됨)
- 외래 키 또는 조인할 때 자주 사용되는 열
- 값이 고유해야 하는 열(고유 인덱스 설정)
- 키 값의 범위가 검색 대상이 되는 열(예: WHERE 절에서 BETWEEN
연산자와 함께 사용되는 열)
- 정렬된 순서로 액세스되는 열(예: ORDER BY 절에서 사용되는 열)
09
Chapter
2. 인덱스
인덱스 작성 지침과 설정
• 다음과 같은 열에는 인덱스를 설정하지 말아야 한다.
- 질의에서 거의 참조하지 않는 열(WHERE 절에서 사용되지 않는 열)
- 고유한 값이 거의 없고, 많은 행을 돌려주는 열(예: bit 형 열 또는‘M’과
‘F’값만 가지는 char(1) 형의 열)
- text, ntext, image 형의 열(인덱스 설정 불가)
• 다음과 같은 열에는 클러스터형 인덱스로 설정하는 것이 좋다.
- 범위 검색을 자주 하는 열
- 정렬된 순서로 자주 액세스되는 열
09
Chapter
2. 인덱스
인덱스 작성 지침과 설정
• 다음과 같은 열에는 클러스터형 인덱스로 설정하지 않는 것이 좋다.
- 크기가 큰 열(이 열 값이 클러스터형 인덱스의 키 값이 되고, 이 키 값은
모든 비클러스터형 인덱스의 리프 수준에서 행 식별자 대신 사용되므로,
공간과 시간 낭비가 커진다)
• 클러스터형 인덱스를 가장 먼저 만들어야 한다. 만약 비클러스터형 인덱스
를 만든 후에 클러스터형 인덱스를 만들면 비클러스터형 인덱스는 자동으
로 재구성된다(테이블에 따라서는 클러스터형 인덱스 하나와 비클러스터
형 인덱스 여러 개를 가질 수 있는데, 이때 클러스터형 인덱스를 마지막에
만든다면 시간이 많이 낭비될 것이다).
09
Chapter
2. 인덱스
인덱스 생성
 인덱스 생성을 위한 주요 구문
• UNIQUE / CLUSTERED / NONCLUSTERED : 고유 / 클러스터형
/ 비클러스터형 인덱스로 만들어준다(UNIQUE와 CLUSTERED/
NONCLUSTERED는 서로 조합될 수 있다).
09
Chapter
2. 인덱스
인덱스 생성
• index_name : 만들 인덱스의 이름이다.
• table_name : 인덱스를 만들 테이블의 이름이다.
• column_name : 인덱스를 만들 열(들)의 이름이다. 둘 이상의 열은 복합
인덱스(complex index)를 만든다.
• ASC / DESC : 오름차순 / 내림차순으로 키 값을 정렬한다(기본 설정은
ASC다).
09
Chapter
2. 인덱스
인덱스 생성
• FILLFACTOR = fillfactor : 채우기 비율을 지정한다. 채우기 비율이란
인덱스를 만들 때 리프 수준의 페이지들을 몇 %나 채울지 지정하는 것이다.
• PAD_INDEX = ON : 인덱스를 만들 때 중간 수준의 페이지들을 채우기
비율과 동일한 비율로 채우게 하는 것이며, 반드시“FILLFACTOR =
fillfactor”옵션과 같이 사용해야 함. PAD_INDEX = OFF는 중간 수준
의 페이지들을 거의 꽉 채움. 기본 값은 OFF다.
09
Chapter
2. 인덱스
인덱스 생성
• IGNORE_DUP_KEY = ON : 고유 인덱스가 설정된 열에 중복된 값을
삽입할 때 경고 메시지를 표시하고 해당 행의 삽입만 실패시키며,
IGNORE_DUP_KEY = OFF는 전체 삽입 작업을 롤백한다. 기본값은
OFF다.
• DROP_EXISTING = ON : 같은 이름으로 이미 존재하는 인덱스를 삭제
하고 다시 만드는데, 인덱스에 조각(fragment)이 많거나 인덱스를 압축
해야 할 경우에 유용하다. 만약 인덱스가 이미 존재하고 있으면 오류를
표시한다. 기본값은 OFF다.
09
Chapter
2. 인덱스
인덱스 생성
 9 인덱스 생성 예를 살펴보자.(1)
예제
- 7행에서 col_unique 열에 대해 고유 클러스터형(UNIQUE CLUSTERED)
인덱스를 만듦
- 6행에서 col_pk 열에 대해 비클러스터형 기본 키 인덱스를 만듦
09
Chapter
2. 인덱스
인덱스 생성
1
2
3
4
5
6
7
8
9
10
USE Test1DB;
IF OBJECT_ID('test1', 'U') IS NOT NULL
DROP TABLE test1;
GO
CREATE TABLE test1 (
col_pk
int
IDENTITY PRIMARY
KEY NONCLUSTERED
, col_unique
char(12)
NULL
UNIQUE
CLUSTERED
, col_complex1 varchar(8) NOT NULL
, col_complex2 varchar(6) NOT NULL
);
09
Chapter
2. 인덱스
인덱스 생성

예제
10
인덱스 생성 예를 살펴보자.(2)
1 USE Test1DB;
2 CREATE INDEX indx_complex1
3
ON test1 (col_complex1, col_complex2)
4
WITH (
5
PAD_INDEX = ON
6
, FILLFACTOR = 50
7
);
8 EXEC sp_helpindex test1;
09
Chapter
2. 인덱스
인덱스 활용과 관리
 생성된 인덱스는 활용하고 관리할 수 있어야 한다. 여기서는 활용의
한 예로 실행 계획을 설명하고, 이어서 인덱스를 삭제하는 방법에 대해
알아보기로 한다.
 실행 계획(execution plan): T-SQL 배치(batch)가 컴파일된
결과물이다. 실행 계획은 질의를 처리할 순서와 방법인데, 이 중에서
핵심적인 부분은 인덱스와 관련된 것이다(쿼리 편집기에서 질의를
실행시킬 때 이 실행 계획을 결과 창에서 확인할 수 있음).
09
Chapter
2. 인덱스
인덱스 활용과 관리
 11
예제
실행 계획을 확인해보자.
- 쿼리 편집기에서 [쿼리 | 실제 실행 계획 포함] 메뉴항목을 선택하든가 ctrl
＋M을 누르면 결과 창에 [실행 계획] 탭이 추가로 나타나고, 이를 누르면
[그림 9-7]과 같은 그래픽적인 실행 계획을 볼 수 있음.
- 실행 계획 표시 기능은 토글 식이므로 다시 ctrl＋M 키를 누를 때까지는 결과
창에 실행 계획이 계속 표시됨
- 이 외에 명령문을 실행하지는 않고 예상되는 실행 계획만 확인할 수도 있음.
이를 위해서는 쿼리 분석기에서 명령문을 선택한 후, [쿼리 | 예상 실행 계획
표시] 항목을 선택하거나 ctrl＋L 키를 누르면 됨
09
Chapter
2. 인덱스
인덱스 활용과 관리
1 USE AdventureWorksLT;
2 SELECT *
3
FROM SalesLT.SalesOrderHeader
4
WHERE CustomerID = 6;
09
Chapter
2. 인덱스
인덱스 활용과 관리
09
Chapter
2. 인덱스
인덱스 삭제
 인덱스를 삭제하는 구문
• index_name은 삭제할 인덱스 이름이다. 한꺼번에 여러 개의 인덱스를
삭제할 수 있다.
• table_name은 인덱스가 속한 테이블 이름이다.
• 제약 조건 PRIMARY KEY 또는 UNIQUE에 의해 만들어진 인덱스는
삭제할 수 없다. 이런 인덱스를 삭제하려면 제약 조건을 삭제해야 한다.
• 삭제된 인덱스가 차지하고 있던 공간은 모두 반환된다.
09
Chapter
2. 인덱스
인덱스 삭제
 12
예제
[그림 9-6]에서 확인했던 인덱스 세 개를 모두 삭제해 보자.
- PRIMARY KEY와 UNIQUE 제약 조건에 의해 만들어진 인덱스는 삭제할
수 없고 오류가 발생하는 것을 알 수 있음
- 일반 인덱스 indx_complex1는 삭제됨
1
2
3
4
5
6
USE Test1DB;
DROP INDEX indx_complex1 ON test1;
GO
DROP INDEX PK__test1__578C3D9A66603565 ON test1;
GO
DROP INDEX UQ__test1__245DF5C36383C8BA ON test1;
09
Chapter
인덱스 삭제
2. 인덱스
09
Chapter
2. 인덱스
SQL Server 2005 / 2008에서의 인덱스 관련 변경 사항
 SQL Server 2005와 2008에서는 인덱스와 관련된 기능들도 많이
바뀌었는데, 이중 중요한 것들만 간략히 설명하기로 한다.
• 파티션 분할된 테이블과 인덱스 : SQL Server 2005부터 여러 파티션
에 걸친 테이블을 만들 수 있고, 각각의 파티션에 대해 인덱스를 만들 수
있다. 이로 인해 대규모의 데이터를 다룰 수 있을 뿐만 아니라, 새 파티션
에 대한 인덱스만 효율적으로 만들 수 있게 되었다.
09
Chapter
2. 인덱스
SQL Server 2005 / 2008에서의 인덱스 관련 변경 사항
• 비클러스터형 인덱스 내의 비키 열(non-key columns) : SQL Server
2005부터 비키 열을 비클러스터형 인덱스에 추가할 수 있다. 이로 인해
쿼리가 북마크 룩업 없이 인덱스 페이지로부터 필요한 모든 것을 추출할
수 있게 되어 데이터 추출 속도가 향상된다. 비키 열들은 비클러스터형
인덱스의 열 개수 제한(16열)과 키 길이 제한(900바이트)에서 제외된다.
비키 열을 추가하려면 CREATE INDEX 문에서“INCLUDE (column_
name, ...)”옵션을 사용하면 된다.
09
Chapter
2. 인덱스
SQL Server 2005 / 2008에서의 인덱스 관련 변경 사항
• 온라인 인덱스 처리 : SQL Server 2005에 추가된 기능으로, 정규 운영
시간에 인덱스를 변경할 수 있게 해준다. 이를 위해서는 CREATE INDEX,
ALTER INDEX, DROP INDEX 및 ALTER TABLE 구문에서 ONLINE
= ON 옵션을 지정하면 된다.
• 인덱스 잠금 정밀도 변경 : SQL Server 2005부터 인덱스 처리시의 잠금
을 제어하는 옵션이 CREATE INDEX와 ALTER INDEX 문에 추가되었
다. 즉“ALLOW_ROW_LOCKS = { ON | OFF }”과“ALLOW_
PAGE_LOCKS = { ON | OFF }”으로 인덱스 처리 시에 행 및 페이지
잠금을 사용할지 여부를 지정할 수 있다(기본값은 둘 다 ON이다).
09
Chapter
2. 인덱스
SQL Server 2005 / 2008에서의 인덱스 관련 변경 사항
• 대규모 인덱스의 삭제 및 재구축 : SQL Server 2005부터 128 익스텐트
이상의 크기를 가지는 인덱스를 보다 효율적으로 처리할 수 있도록 데이터
베이스 엔진이 변경되었다. 즉 이렇게 큰 인덱스를 삭제 또는 재구축할 때
는 논리적인 페이지 제거(deallocation)만으로 신속히 처리를 하고, 물
리적인 실제 페이지 제거는 그 후 백그라운드에서 일괄 작업으로 진행된
다. 따라서 긴 잠금 시간을 피할 수 있어서 성능이 개선되는 것이다.
09
Chapter
2. 인덱스
SQL Server 2005 / 2008에서의 인덱스 관련 변경 사항
• 필터링된 인덱스 : SQL Server 2008부터 테이블의 일부 행들에 대해서
필터링된 인덱스를 만들 수 있다. 이전 버전에서는 테이블의 모든 행들에
대해서만 인덱스를 만들 수 있었다. 필터링된 인덱스를 만들려면 CREATE
INDEX 문에서 WHERE 절을 사용하면 된다(SELECT 문의 WHERE 절
과 비슷하다).
• 테이블과 인덱스의 압축된 저장소 : SQL Server 2008부터 테이블,
인덱스 및 인덱싱된 뷰를 위한 행 및 페이지 형식의 저장소 압축을 지원한
다. 파티션된 테이블과 인덱스에서는 각 파티션에서의 독립적인 압축도
가능하다.
09
Chapter
2. 인덱스
SQL Server 2005 / 2008에서의 인덱스 관련 변경 사항
• 공간(spatial) 인덱스 : SQL Server 2008부터 geometry와
geography라는 공간 데이터 형식을 지원하는데, 이런 형식의 열에
만들 수 있는 인덱스가 바로 공간 인덱스다. 공간 인덱스는 .NET CLR을
이용하여 구현된다.