CREATE INDEX(Transact-SQL)

테이블(힙)에 클러스터형 인덱스를 만들거나 기존 클러스터형 인덱스를 삭제하고 다시 만들려면 데이터베이스에서 데이터 정렬 및 원본 테이블의 임시 사본이나 기존 클러스터형 인덱스 데이터의 임시 사본을 보관할 수 있는 추가 작업 영역이 필요합니다. 자세한 내용은 인덱스 디스크 공간 요구 사항 확인을 참조하십시오. 클러스터형 인덱스에 대한 자세한 내용은 클러스터형 인덱스 만들기를 참조하십시오.

고유 인덱스가 있으면 데이터베이스 엔진은 삽입 작업으로 데이터를 추가할 때마다 중복 값이 있는지 확인합니다. 중복 키 값을 생성하는 삽입 작업은 롤백되며 데이터베이스 엔진에서는 오류 메시지를 표시합니다. 이런 현상은 삽입 작업을 통해 많은 행을 변경할 때 중복 값이 하나만 있어도 발생합니다. 고유 인덱스가 있고 IGNORE_DUP_KEY 절이 ON으로 설정된 경우 데이터를 입력하려고 하면 UNIQUE 인덱스를 위반하는 행만 실패합니다. 고유 인덱스에 대한 자세한 내용은 고유 인덱스 만들기를 참조하십시오.

분할된 인덱스는 분할된 테이블과 비슷한 방법으로 만들어지며 유지 관리됩니다. 그러나 보통 인덱스와 같이 별도의 데이터베이스 개체로 처리됩니다. 분할되지 않은 테이블에 분할된 인덱스가 있을 수 있으며 분할된 테이블에 분할되지 않은 인덱스가 있을 수 있습니다.

분할된 테이블에 인덱스를 만들고 인덱스를 배치할 파일 그룹을 지정하지 않으면 인덱스는 기본 테이블과 같은 방법으로 분할됩니다. 이렇게 되는 이유는 기본적으로 인덱스가 기본 테이블처럼 동일한 파일 그룹에 배치되고 동일한 분할 열을 사용하는 동일한 파티션 구성표의 분할된 테이블에 대해 배치되기 때문입니다.

비고유 클러스터형 인덱스를 분할하는 경우 데이터베이스 엔진은 기본적으로 지정되지 않은 모든 분할 열을 클러스터형 인덱스 키 목록에 추가합니다.

인덱싱된 뷰는 테이블의 인덱스와 같은 방법으로 분할된 테이블에 만들 수 있습니다. 분할된 인덱스에 대한 자세한 내용은 분할된 테이블 및 인덱스를 참조하십시오.

뷰에 고유 클러스터형 인덱스를 만들면 클러스터형 인덱스가 있는 테이블의 저장 방식과 마찬가지로 데이터베이스에 뷰가 저장되므로 쿼리 성능이 향상됩니다. 쿼리 최적화 프로그램은 인덱싱된 뷰를 사용하여 쿼리 실행 속도를 높일 수 있습니다. 최적화 프로그램이 인덱싱된 뷰를 대신 사용하므로 쿼리에서 해당 뷰를 참조할 필요가 없습니다.

다음 단계는 인덱싱된 뷰를 만들고 뷰를 성공적으로 구현하는 데 필요합니다.

1. 뷰에 참조될 기존의 모든 테이블에 대해 SET 옵션이 올바른지 확인합니다.

2. 새 테이블 및 뷰를 만들기 전에 세션에 SET 옵션이 올바르게 설정되어 있는지 확인합니다.

3. 뷰 정의가 결정적인지 확인합니다.

4. WITH SCHEMABINDING 옵션을 사용하여 뷰를 만듭니다.

5. 뷰에 고유 클러스터형 인덱스를 만듭니다.

인덱싱된 뷰에 필요한 SET 옵션

쿼리가 실행될 때 다른 SET 옵션이 활성화되어 있으면 같은 식을 계산해도 데이터베이스 엔진에서 다른 결과가 나올 수 있습니다. 예를 들어 SET 옵션 CONCAT_NULL_YIELDS_NULL이 ON으로 설정된 후 식 'abc' + NULL의 결과로 NULL 값이 반환됩니다. 그러나 CONCAT_NULL_YIEDS_NULL을 OFF로 설정한 후 같은 식의 결과는 **'abc'**가 됩니다.

뷰를 올바르게 유지하고 일관된 결과를 반환하게 하려면 인덱싱된 뷰는 몇 가지 SET 옵션에 대해 고정 값이 필요합니다. 다음 테이블의 SET 옵션은 다음 상황이 발생할 때마다 필요한값 열에 표시된 값으로 설정되어야 합니다.

• 인덱싱된 뷰를 만듭니다.

• 인덱싱된 뷰에 참가하는 테이블에서 삽입, 업데이트 또는 삭제 작업이 수행됩니다. 여기에는 대량 복사, 복제, 분산 쿼리 등의 작업이 포함됩니다.

• 쿼리 최적화 프로그램에서 인덱싱된 뷰를 사용하여 쿼리 계획을 만듭니다.

  SET 옵션	필요한 값	기본 서버 값	기본값 OLE DB 및 ODBC 값	기본 DB-Library 값
  ANSI_NULLS	ON	ON	ON	OFF
  ANSI_PADDING	ON	ON	ON	OFF
  ANSI_WARNINGS*	ON	ON	ON	OFF
  ARITHABORT	ON	ON	OFF	OFF
  CONCAT_NULL_YIELDS_NULL	ON	ON	ON	OFF
  NUMERIC_ROUNDABORT	OFF	OFF	OFF	OFF
  QUOTED_IDENTIFIER	ON	ON	ON	OFF

  *데이터베이스 호환성 수준이 90 이상으로 설정된 경우 ANSI_WARNINGS를 ON으로 설정하면 암시적으로 ARITHABORT가 ON으로 설정됩니다. 데이터베이스 호환성 수준이 80 이하로 설정된 경우에는 명시적으로 ARITHABORT 옵션을 ON으로 설정해야 합니다.

OLE DB 또는 ODBC 서버 연결을 사용하는 경우 수정해야 하는 유일한 값은 ARITHABORT 설정입니다. 모든 DB-Library 값은 sp_configure를 사용하여 서버 수준에서 또는 SET 명령을 사용하여 응용 프로그램에서 올바르게 설정해야 합니다. SET 옵션에 대한 자세한 내용은 SQL Server에서 옵션 사용을 참조하십시오.

중요
서버의 데이터베이스에서 계산 열에 첫 번째로 인덱싱된 뷰 또는 인덱스가 만들어지면 바로 서버 차원에서 ARITHABORT 사용자 옵션을 ON으로 설정하는 것이 좋습니다.

결정적 함수

추가 요구 사항

필터링된 인덱스는 테이블에서 적은 비율의 행을 선택하는 쿼리에 적합한 최적화된 비클러스터형 인덱스입니다. 이 인덱스에서는 필터 조건자를 사용하여 테이블의 일부 데이터를 인덱싱합니다. 잘 디자인된 필터링된 인덱스는 쿼리 성능을 개선하고 저장소 비용과 유지 관리 비용을 줄일 수 있습니다.

필터링된 인덱스에 필요한 SET 옵션_

공간 인덱스에 대한 자세한 내용은 CREATE SPATIAL INDEX(Transact-SQL) 및 공간 인덱스 작업(데이터베이스 엔진)을 참조하십시오.

XML 인덱스에 대한 자세한 내용은 CREATE XML INDEX(Transact-SQL) 및 XML 데이터 형식 열의 인덱스를 참조하십시오.

인덱스 키의 최대 크기는 900바이트입니다. 900바이트를 초과하는 varchar 열에 있는 기존 데이터가 900바이트를 초과하지 않는 경우 이 열에 900바이트를 초과하는 인덱스를 만들 수 있습니다. 그러나 전체 크기가 900바이트를 초과하는 열에서 다음 삽입 작업이나 업데이트 동작이 실패합니다. 자세한 내용은 인덱스 키의 최대 크기를 참조하십시오. 클러스터형 인덱스의 인덱스 키는 기존 데이터가 ROW_OVERFLOW_DATA 할당 단위에 있는 varchar 열을 포함할 수 없습니다. 클러스터형 인덱스를 varchar 열에 만들고 기존 데이터가 IN_ROW_DATA 할당 단위에 있는 경우에는 데이터를 행 외부로 밀어넣을 열에서 다음 삽입 작업이나 업데이트 동작이 실패합니다. 할당 단위에 대한 자세한 내용은 테이블 및 인덱스 구성을 참조하십시오.

비클러스터형 인덱스는 인덱스의 리프 수준에 키가 아닌 열을 포함할 수 있습니다. 인덱스 키 크기를 계산할 때 데이터베이스 엔진은 이러한 열을 계산하지 않습니다. 자세한 내용은 포괄 열이 있는 인덱스를 참조하십시오.

계산 열에 인덱스를 만들 수 있습니다. 또한 계산 열은 PERSISTED 속성을 가질 수 있습니다. 즉, 데이터베이스 엔진은 계산된 값을 테이블에 저장하고 계산 열이 종속된 다른 열이 업데이트되면 해당 값을 업데이트합니다. 데이터베이스 엔진은 열에 인덱스를 만들 때와 이 인덱스가 쿼리에서 참조될 때 이러한 지속형 값을 사용합니다.

계산 열을 인덱싱하려면 계산 열이 결정적이고 정확해야 합니다. 그러나 PERSISTED 속성을 사용하면 인덱싱할 수 있는 계산 열 유형이 다음을 포함하도록 확장할 수 있습니다.

• Transact-SQL을 기반으로 하는 계산 열 및 사용자가 결정적으로 표시한 CLR 사용자 정의 형식 메서드 및 CLR 함수

• 데이터베이스 엔진이 정의한 대로 결정적이지만 정확하지 않은 식을 기반으로 하는 계산 열

지속형 계산 열의 경우 다음 SET 옵션을 앞의 "인덱싱된 뷰에 필요한 SET 옵션" 섹션에 나온 것처럼 설정해야 합니다.

UNIQUE나 PRIMARY KEY 제약 조건은 인덱싱을 위해 모든 조건을 충족하는 한 계산 열을 포함할 수 있습니다. 특히 계산 열은 결정적이고 정확해야 하거나 결정적이고 지속되어야 합니다. 결정성에 대한 자세한 내용은 결정적 함수 및 비결정적 함수를 참조하십시오.

image, ntext, text, varchar(max), nvarchar(max), varbinary(max) 및 xml 데이터 형식에서 파생된 계산 열은 계산 열 데이터 형식이 인덱스 키 열 또는 키가 아닌 열에 허용되는 경우에 한해 키 또는 키가 아닌 포괄 열로 인덱싱될 수 있습니다. 예를 들어 계산 xml 열에 기본 XML 인덱스를 만들 수 없습니다. 인덱스 키 크기가 900바이트를 초과하면 경고 메시지가 표시됩니다.

계산 열에 인덱스를 만들면 이전에 작업한 삽입 또는 업데이트 작업이 실패할 수 있습니다. 계산 열에서 산술 오류가 발생하는 경우 이러한 실패가 발생할 수 있습니다. 예를 들어 다음 테이블의 계산 열 c에 산술 오류가 발생해도 INSERT 문은 실행됩니다.

CREATE TABLE t1 (a int, b int, c AS a/b);
INSERT INTO t1 VALUES (1, 0);

대신 테이블을 만든 후 계산 열 c에 인덱스를 만들면 동일한 INSERT 문이 이번에는 실패합니다.

CREATE TABLE t1 (a int, b int, c AS a/b);
CREATE UNIQUE CLUSTERED INDEX Idx1 ON t1(c);
INSERT INTO t1 VALUES (1, 0);

자세한 내용은 계산 열에 인덱스 만들기를 참조하십시오.

포괄 열이라고 하는 키가 아닌 열은 비클러스터형 인덱스의 리프 수준에 추가되어 쿼리를 포함함으로써 쿼리 성능을 향상시킬 수 있습니다. 즉, 쿼리에서 참조되는 모든 열은 키 열 또는 키가 아닌 열로 인덱스에 포함됩니다. 따라서 쿼리 최적화 프로그램은 인덱스 스캔에서 필요한 모든 정보를 찾을 수 있으므로 테이블 또는 클러스터형 인덱스 데이터에 액세스되지 않습니다. 자세한 내용은 포괄 열이 있는 인덱스를 참조하십시오.

SQL Server 2005에는 새 인덱스 옵션이 추가되었으며 옵션 지정 방법도 수정되었습니다. 이전 버전과 호환되는 구문에서 WITH option_name은 WITH ( <option_name> = ON **)**과 같습니다. 인덱스 옵션을 설정하면 다음 규칙이 적용됩니다.

• 새 인덱스 옵션은 WITH (option_name= ON | OFF**)**만 사용하여 지정할 수 있습니다.

• 옵션은 동일한 문에 이전 버전과 호환되는 구문 및 새 구문 모두를 사용하여 지정할 수 없습니다. 예를 들어 WITH (DROP_EXISTING, ONLINE = ON**)**을 지정하면 이 문이 실패합니다.

• XML 인덱스를 만드는 경우 옵션은 WITH (option_name= ON | OFF**)**를 사용하여 지정해야 합니다.

DROP_EXISTING 절을 사용하여 인덱스 다시 작성, 열 추가 또는 삭제, 옵션 수정, 열 정렬 순서 수정 또는 파티션 구성표나 파일 그룹 변경 등의 작업을 수행할 수 있습니다.

인덱스가 PRIMARY KEY 또는 UNIQUE 제약 조건을 강제 적용하고 인덱스 정의가 변경되지 않으면 이 인덱스는 삭제되고 기존 제약 조건을 보존한 상태에서 다시 만들어집니다. 그러나 인덱스 정의가 변경되면 이 문은 실패합니다. PRIMARY KEY 또는 UNIQUE 제약 조건의 정의를 변경하려면 제약 조건을 삭제하고 새 정의가 있는 제약 조건을 추가합니다.

비클러스터형 인덱스가 있는 테이블에서 동일하거나 서로 다른 키 집합을 사용하여 클러스터형 인덱스를 다시 만들 때 DROP_EXISTING을 사용하면 성능이 향상됩니다. DROP_EXISTING은 이전에 클러스터형 인덱스에 대해 DROP INDEX 문을 실행한 다음 새로 클러스터형 인덱스를 만드는 CREATE INDEX 문을 실행하는 것과 같습니다. 비클러스터형 인덱스는 인덱스 정의가 변경된 경우에만 다시 한 번 만들어집니다. 인덱스 정의에 원래 인덱스와 같은 인덱스 이름, 키 및 파티션 열, 고유성 특성 및 정렬 순서가 있으면 DROP_EXISTING 절은 비클러스터형 인덱스를 다시 만들지 않습니다.

비클러스터형 인덱스는 다시 만들지 여부에 관계없이 항상 원래 파일 그룹 또는 파티션 구성표에 남아 있으며 원래 파티션 함수를 사용합니다. 클러스터형 인덱스를 다른 파일 그룹이나 파티션 구성표에 다시 만들면 비클러스터형 인덱스는 클러스터형 인덱스의 새 위치와 일치하도록 이동되지 않습니다. 따라서 비클러스터형 인덱스가 이전에 클러스터형 인덱스와 일치하도록 맞춰진 경우에도 일치하지 않을 수 있습니다. 분할된 인덱스 정렬에 대한 자세한 내용은 분할 인덱스에 대한 특수 지침을 참조하십시오.

인덱스 문이 비클러스터형 인덱스를 지정하고 ONLINE 옵션이 OFF로 설정되어 있지 않은 경우 동일 인덱스 키 열이 동일한 순서의 동일한 오름차순 또는 내림차순을 사용하면 DROP_EXISTING 절은 데이터를 다시 정렬하지 않습니다. 클러스터형 인덱스가 해제되면 ONLINE을 OFF로 설정한 상태에서 CREATE INDEX WITH DROP_EXISTING 작업을 수행해야 합니다. 비클러스터형 인덱스가 해제되고 해제된 클러스터형 인덱스와 관련이 없는 경우에는 ONLINE을 OFF 또는 ON으로 설정한 상태에서 CREATE INDEX WITH DROP_EXISTING 작업을 수행할 수 있습니다.

익스텐트가 128 이상인 인덱스를 삭제하거나 다시 작성하면 데이터베이스 엔진은 트랜잭션이 커밋될 때까지 실제 페이지 할당 취소 및 이와 관련된 잠금을 지연합니다. 자세한 내용은 큰 개체 삭제 및 다시 작성을 참조하십시오.

다음 지침은 인덱스 작업을 온라인 상태로 수행할 때 적용됩니다.

• 온라인 인덱스 작업이 진행되는 동안에는 기본 테이블을 변경하거나 자르거나 삭제할 수 없습니다.

• 인덱스 작업 중에 임시 디스크 공간이 추가로 필요합니다. 자세한 내용은 인덱스 디스크 공간 요구 사항 확인을 참조하십시오.

• 온라인 작업은 분할된 인덱스 및 지속형 계산 열이 들어 있는 인덱스 또는 포괄 열에서 수행될 수 있습니다.

자세한 내용은 온라인으로 인덱스 작업 수행을 참조하십시오.

ALLOW_ROW_LOCKS = ON이고 ALLOW_PAGE_LOCK = ON이면 인덱스에 액세스할 때 행, 페이지 및 테이블 수준 잠금이 허용됩니다. 데이터베이스 엔진에서는 적절한 잠금을 선택하고 행 또는 페이지 잠금에서 테이블 잠금으로 에스컬레이션할 수 있습니다. 자세한 내용은 잠금 에스컬레이션(데이터베이스 엔진)을 참조하십시오.

ALLOW_ROW_LOCKS = OFF이고 ALLOW_PAGE_LOCK = OFF이면 인덱스에 액세스할 때 테이블 수준 잠금만 허용됩니다.

인덱스에 대한 잠금 세분성을 구성하는 방법은 인덱스 잠금 사용자 지정을 참조하십시오.

인덱스 정보를 반환하려면 카탈로그 뷰, 시스템 함수 및 시스템 저장 프로시저를 사용할 수 있습니다. 자세한 내용은 인덱스 정보 보기를 참조하십시오.

데이터 압축에 대한 자세한 내용은 압축된 테이블 및 인덱스 만들기 항목을 참조하십시오. 고려해야 할 주요 요소는 다음과 같습니다.

• 압축하면 한 페이지에 더 많은 행을 저장할 수 있지만 최대 행 크기는 변경되지 않습니다.

• 인덱스의 비-리프 페이지는 압축된 페이지가 아니지만 행은 압축할 수 있습니다.

• 각각의 비클러스터형 인덱스에는 개별 압축 설정이 있으며 기본 테이블의 압축 설정을 상속하지 않습니다.

• 힙에 클러스터형 인덱스를 만드는 경우 이 클러스터형 인덱스는 다른 압축 상태를 지정하지 않는 한 힙의 압축 상태를 상속합니다.

다음은 분할된 인덱스에 적용되는 제한 사항입니다.

• 테이블에 정렬되지 않은 인덱스가 있으면 단일 파티션의 압축 설정을 변경할 수 없습니다.

• ALTER INDEX <index> ... REBUILD PARTITION ... 구문은 인덱스의 지정된 파티션을 다시 작성합니다.

• ALTER INDEX <index> ... REBUILD WITH ... 구문은 인덱스의 모든 파티션을 다시 작성합니다.

압축 상태를 변경할 경우 테이블, 인덱스 또는 파티션에 어떤 영향을 주는지 확인하려면 sp_estimate_data_compression_savings 저장 프로시저를 사용합니다.

다음 예에서는 Purchasing.ProductVendor 테이블의 VendorID 열에 비클러스터형 인덱스를 만듭니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_ProductVendor_VendorID')
    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;
GO
CREATE INDEX IX_ProductVendor_VendorID 
    ON Purchasing.ProductVendor (VendorID); 
GO

다음은 Sales.SalesPerson 테이블의 SalesQuota 및 SalesYTD 열에 비클러스터형 복합 인덱스를 만드는 예입니다.

USE AdventureWorks
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_SalesPerson_SalesQuota_SalesYTD')
    DROP INDEX IX_SalesPerson_SalesQuota_SalesYTD ON Sales.SalesPerson ;
GO
CREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
    ON Sales.SalesPerson (SalesQuota, SalesYTD);
GO

다음 예에서는 Production.UnitMeasure 테이블의 Name 열에 고유 비클러스터형 인덱스를 만듭니다. 인덱스는 Name 열에 삽입된 데이터의 고유성을 강제 적용합니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT name from sys.indexes
             WHERE name = N'AK_UnitMeasure_Name')
    DROP INDEX AK_UnitMeasure_Name ON Production.UnitMeasure;
GO
CREATE UNIQUE INDEX AK_UnitMeasure_Name 
    ON Production.UnitMeasure(Name);
GO

다음 쿼리는 기존 행과 동일한 값을 가진 행을 삽입하여 고유성 제약 조건을 테스트합니다.

--Verify the existing value.
SELECT Name FROM Production.UnitMeasure WHERE Name = N'Ounces';
GO
INSERT INTO Production.UnitMeasure (UnitMeasureCode, Name, ModifiedDate)
    VALUES ('OC', 'Ounces', GetDate());

결과 오류 메시지는 다음과 같습니다.

Server: Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'UnitMeasure' with unique index 'AK_UnitMeasure_Name'. The statement has been terminated.

다음은 IGNORE_DUP_KEY 옵션을 먼저 ON으로 설정한 후 다시 OFF로 설정한 상태에서 여러 행을 임시 테이블에 삽입했을 때 미치는 영향을 보여 주는 예입니다. 두 번째 여러 행 INSERT 문이 실행될 때 의도적으로 중복 값을 발생시키는 #Test 테이블에 단일 행을 삽입합니다. 테이블의 행 수가 삽입된 행 수를 반환합니다.

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = ON);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

두 번째 INSERT 문의 결과는 다음과 같습니다.

Server: Msg 3604, Level 16, State 1, Line 5 Duplicate key was ignored.

Number of rows 
-------------- 
38

고유성 제약 조건을 위반하지 않은 Production.UnitMeasure 테이블에서 삽입된 행이 성공적으로 삽입되었습니다. 경고가 발생하고 중복된 행이 무시되었지만 전체 트랜잭션은 롤백되지 않았습니다.

같은 문이 다시 실행되지만 IGNORE_DUP_KEY를 OFF로 설정한 상태입니다.

USE AdventureWorks;
GO
CREATE TABLE #Test (C1 nvarchar(10), C2 nvarchar(50), C3 datetime);
GO
CREATE UNIQUE INDEX AK_Index ON #Test (C2)
    WITH (IGNORE_DUP_KEY = OFF);
GO
INSERT INTO #Test VALUES (N'OC', N'Ounces', GETDATE());
INSERT INTO #Test SELECT * FROM Production.UnitMeasure;
GO
SELECT COUNT(*)AS [Number of rows] FROM #Test;
GO
DROP TABLE #Test;
GO

두 번째 INSERT 문의 결과는 다음과 같습니다.

Server: Msg 2601, Level 14, State 1, Line 5
Cannot insert duplicate key row in object '#Test' with unique index
'AK_Index'. The statement has been terminated.

Number of rows 
-------------- 
1

Production.UnitMeasure 테이블에서 오직 한 행만 UNIQUE 인덱스 제약 조건을 위반했지만 이 테이블에서 어떤 행도 삽입되지 않았습니다.

다음은 DROP_EXISTING 옵션을 사용하여 Production.WorkOrder 테이블의 ProductID 열에서 기존 인덱스를 삭제하고 다시 만드는 예제입니다. FILLFACTOR 및 PAD_INDEX 옵션도 설정됩니다.

USE AdventureWorks;
GO
CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID
    ON Production.WorkOrder(ProductID)
    WITH (FILLFACTOR = 80,
        PAD_INDEX = ON,
        DROP_EXISTING = ON);
GO

다음 예에서는 뷰를 만들고 이 뷰에 인덱스를 만듭니다. 인덱싱된 뷰를 사용하는 두 개의 쿼리가 포함되어 있습니다.

USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

다음 예에서는 한 개의 키 열(PostalCode) 및 네 개의 키가 아닌 열(AddressLine1, AddressLine2, City, StateProvinceID)이 있는 비클러스터형 인덱스를 만듭니다. 인덱스에서 처리하는 쿼리가 이어집니다. 쿼리 최적화 프로그램에서 선택한 인덱스를 표시하려면 쿼리를 실행하기 전에 SQL Server Management Studio의 쿼리 메뉴에서 실제 실행 계획 표시를 선택합니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
            WHERE name = N'IX_Address_PostalCode')
    DROP INDEX IX_Address_PostalCode ON Person.Address;
GO
CREATE NONCLUSTERED INDEX IX_Address_PostalCode
    ON Person.Address (PostalCode)
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
GO

다음은 기존 파티션 구성표인 TransactionsPS1에 분할된 비클러스터형 인덱스를 만드는 예입니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'IX_TransactionHistory_ReferenceOrderID'
    AND object_id = OBJECT_ID(N'Production.TransactionHistory'))
DROP INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory;
GO
CREATE NONCLUSTERED INDEX IX_TransactionHistory_ReferenceOrderID
    ON Production.TransactionHistory (ReferenceOrderID)
    ON TransactionsPS1 (TransactionDate);
GO

다음 예에서는 Production.BillOfMaterials 테이블에 필터링된 인덱스를 만듭니다. 필터 조건자는 필터링된 인덱스에 키 열이 아닌 열을 포함할 수 있습니다. 이 예에서 조건자는 EndDate가 NULL이 아닌 행만 선택합니다.

USE AdventureWorks;
GO
IF EXISTS (SELECT name FROM sys.indexes
    WHERE name = N'FIBillOfMaterialsWithEndDate' 
    AND object_id = OBJECT_ID(N'Production.BillOfMaterials'))
DROP INDEX FIBillOfMaterialsWithEndDate
    ON Production.BillOfMaterials;
GO
CREATE NONCLUSTERED INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;
GO

다음 예에서는 행 압축을 사용하여 분할되지 않은 테이블에 인덱스를 만듭니다.

CREATE NONCLUSTERED INDEX IX_INDEX_1 
    ON T1 (C2)
WITH ( DATA_COMPRESSION = ROW ) ; 
GO

다음 예에서는 인덱스의 모든 파티션에서 행 압축을 사용하여 분할된 테이블에 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH ( DATA_COMPRESSION = ROW ) ;
GO

다음 예에서는 인덱스의 1 파티션에서 페이지 압축을 사용하고 2-4 파티션에서 행 압축을 사용하여 분할된 테이블에 인덱스를 만듭니다.

CREATE CLUSTERED INDEX IX_PartTab2Col1
ON PartitionTable1 (Col1)
WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1),
    DATA_COMPRESSION = ROW ON PARTITIONS (2 TO 4 ) ) ;
GO