|
CREATE INDEX 명령에 대한 설명과 기능에 대해서 살펴보고, 각 기능을 설명하는 예제
구문과 코드 예제를 소개합니다.
■ Create Index 구문
CREATE INDEX 명령은 인덱스를 생성하는 역할을 합니다. SQL Server 2005에서는 새로운
인덱스 관련 기능을 지원하기 위해 CREATE INDEX 구문에 대한 기능이 확장되었습니다.
새로운 인덱스 관련 기능을 사용하기 위해서, SQL Server Management Studio의 개체
탐색기의 새 인덱스 생성 대화상자를 사용할 수 있습니다.
[참고]
CREATE INDEX 명령에서 유지 관리 마법사에서 사용하는 PAD_INDEX, SORT_IN_TEMPdb, IGNORE_DUP_KEY, ONLINE 옵션도 지정하여 사용할 수 있습니다.
CREATE [UNIQUE][CLUSTERED | NONCLUSTERED] INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]
{table_or_view_name}(column [ASC | DESC][,...n])
[INCLUDE (column_name[,...n])]
[WITH(<relational_index_option>[,...n])]
[ON {partition_scheme_name(column_name[,...n])
| filegroup_name | DEFAULT}]
<relation_index_option>::=
{ PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| IGNORE_DUP_KEY = {ON | OFF}
| STATISTICS_NO_RECOMPUTE = {ON | OFF} | DROP_EXISTING = {ON | OFF}
| ONLINE = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = number_of_processors }
■ 온라인 인덱스 작업
CREATE INDEX 구문의 WITH 절에 ONLINE=ON 옵션을 지정하면, 인덱스 생성작업을 다른 DML 작업과 병행해서 수행할 수 있습니다.
[참고]
xml, text, ntext, image, varchar(max), nvarchar(max), varbinary(max), filestream 데이터형이
사용된 테이블의 경우에는 온라인 인덱스 작업을 수행할 수 없습니다.
CREATE INDEX IX_Employee_ManagerID
ON HumanResources.Employee (ManagerID)
WITH (ONLINE=ON)
■ 병렬처리수준 지정
기본값으로, 데이터베이스 엔진에서는 최대병렬처리수준(MAXDOP) 구성옵션의 값을
기준으로 인덱스를 생성하는 작업에서 사용할 최대 CPU 수를 결정하게 됩니다. 필요에 따라,
인덱스 작업과 병행하여 처리되는 다른 작업에 영향을 최소화하기 위해, 별도의 MAXDOP
옵션을 지정하여, 인덱스 작업에서 사용하는 CPU를 제한할 수 있습니다.
MAXDOP 옵션을 지정하면, 서버에 구성되어 있는 최대병렬처리수준(MAXDOP) 옵션값보다
우선하여 적용됩니다. MAXDOP =0이라고 지정하면, 현재 데이터베이스 엔진의 작업부하를
고려한 상태에서, 사용할 수 있는 전체 CPU를 인덱스 작업에 사용합니다.
[참고]
ONLINE 옵션과 함께, 인덱스 생성, 변경, 삭제 작업에만 적용되는 별도의 MAXDOP 옵션을
설정할 수 있습니다. 쿼리 최적화기에서는 해당 인덱스를 참조하는 쿼리를 최적화하기
위해서 기존에 설정된 최대병렬처리수준 (MAXDOP) 구성을 사용합니다.
CREATE INDEX IX_Employee_ManagerID
ON HumanResources.Employee (ManagerID)
WITH (ONLINE=ON, MAXDOP = 3)
■ 인덱스에 포함할 컬럼 지정
인덱스 리프(leaf) 노드에 포함될 컬럼을 추가로 지정할 수 있습니다.
커버된 인덱스를 생성하기 위해, 예제와 같이, AddressLine1 컬럼과, AddressLine2 컬럼을 AddressID컬럼에 대한 인덱스에 추가할 수 있습니다. 하지만, 해당 테이블에 대한 INSERT, UPDATE, DELETE 작업을 수행할 때는, 인덱스에 중복된 데이터를 유지하기 위해 추가
오버헤드가 발생하게 됩니다.
text, ntext, image 데이터형 컬럼에 대해서는 사용할 수 없습니다.
CREATE INDEX IX_AddressDetails
ON Person.Address (AddressID)
INCLUDE (AddressLine1, AddressLine2)
■ 인덱스 분할(Partition)
CREATE INDEX 명령에 파일그룹과 파티션 스키마를 지정하여 분할 인덱스를 정의할 수
있습니다.
인덱스가 설정된 테이블은 분할된 상태이고, 인덱스 분할을 지정하지 않은 상태라면, 해당
인덱스는 테이블이 사용하고 있는 파티션 스키마와 동일한 위치에 생성됩니다.
CREATE INDEX IX_CustomerDetails
ON Sales.Customer(CustomerID)
ON Sales.CustomerScheme(CustomerID)
■ XML 인덱스 생성
CREATE [PRIMARY] XML INDEX 명령을 사용하여 XML 인덱스를 생성할 수 있습니다.
테이블에 XML 인덱스를 생성하기 전에 해당 테이블에 반드시 클러스터형 인덱스를 생성해야 합니다. 부가(secondary) XML 인덱스를 생성하기 전에, 해당 XML 컬럼에 기본(primary)
XML 인덱스가 생성되어 있어야 합니다. XML 인덱스를 생성할 때는, ONLINE 옵션을 사용할
수 없습니다.
CREATE [PRIMARY] XML INDEX index_name
ON [{database_name.[schema_name]. | schema_name.}]table_name
(xml_column_name)
[ USING XML INDEX xml_index_name
[ FOR { VALUE | PATH } ]
[WITH(<xml_index_option>[,...n])]
<xml_index_option>::=
{ PAD_INDEX = {ON | OFF}
| FILLFACTOR = fillfactor
| SORT_IN_TEMPDB = {ON | OFF}
| STATISTICS_NO_RECOMPUTE = {ON | OFF}
| DROP_EXISTING = {ON | OFF}
| ALLOW_ROW_LOCKS = {ON | OFF}
| ALLOW_PAGE_LOCKS = {ON | OFF}
| MAXDOP = number_of_processors }
다음과 같은 구문으로 XML 인덱스를 생성할 수 있습니다.
CREATE PRIMARY
XML INDEX IXML_ContactAddContact
ON Person.Contact(AdditionalContactInfo) |