CREATE COLUMNSTORE INDEX(Transact-SQL)

SQL Server 테이블에 비클러스터형 메모리 내 columnstore 인덱스를 만듭니다. 비클러스터형 columnstore 인덱스를 사용하면 columnstore 압축을 활용해서 읽기 전용 데이터에 대한 쿼리 실행 시간을 크게 줄일 수 있습니다.

클러스터형 columnstore 인덱스를 만들려면 CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL)를 참조하십시오.

자세한 내용은 다음 항목을 참조하십시오.

적용 대상: SQL Server(SQL Server 2012 - 현재 버전). )

항목 링크 아이콘 Transact-SQL 구문 표기 규칙

구문

Create a non-clustered columnstore index.
CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON [database_name. [schema_name ] . | schema_name . ] table_name  
        ( column  [ ,...n ] )
    [ WITH ( <columnstore_index_option> [ ,...n ] ) ]
    [ ON {
        partition_scheme_name ( column_name ) 
        | filegroup_name 
        | "default" 
        } 
    ]
[ ; ]

<columnstore_index_option> ::=
{
      DROP_EXISTING = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
 }

인수

index_name
인덱스의 이름을 지정합니다. index_name 은 테이블에서 고유해야 하지만 데이터베이스에서 고유할 필요는 없습니다. 인덱스 이름은 식별자 규칙을 따라야 합니다.

비클러스터형 columnstore 인덱스의 경우
( column [ ,...n ] )
저장할 열을 지정합니다. 비클러스터형 columnstore 인덱스는 1024개 열로 제한됩니다.

각 열은 columnstore 인덱스에 대해 지원되는 데이터 형식이어야 합니다. 지원되는 데이터 형식 목록은 제한 사항을 참조하십시오.
ON [database_name. [schema_name ] . | schema_name . ] table_name
인덱스를 포함할 테이블의 1, 2 또는 3 부분 이름을 지정합니다.
ON
이러한 옵션은 인덱스를 만들 파일 그룹을 지정합니다.
- partition_scheme_name ( column_name )
  분할된 인덱스의 파티션이 매핑될 파일 그룹을 정의하는 파티션 구성표를 지정합니다. 파티션 구성표는 CREATE PARTITION SCHEME을 실행하여 데이터베이스 내에 포함해야 합니다. column_name은 분할된 인덱스가 분할될 경우 기준으로 사용할 열을 지정합니다. 이 열은 partition_scheme_name에서 사용하는 파티션 함수의 데이터 형식, 길이 및 전체 자릿수가 일치해야 합니다. column_name은 인덱스 정의의 열만 사용할 필요는 없으며 columnstore 인덱스를 분할하는 경우 데이터베이스 엔진에서는 인덱스의 열이 아직 지정되지 않은 경우 분할 열을 인덱스의 열로 추가합니다.
  
  partition_scheme_name 또는 filegroup이 지정되지 않고 테이블이 분할된 경우 인덱스는 동일한 분할 열을 사용하여 동일한 파티션 구성표에 기본 테이블로 배치됩니다.
  
  분할된 테이블의 Columnstore 인덱스는 파티션 정렬됩니다.
  
  인덱스를 분할하는 방법은 분할된 테이블 및 인덱스을 참조하십시오.
- filegroup_name
  인덱스를 만들 파일 그룹 이름을 지정합니다. filegroup_name이 지정되지 않고 테이블이 분할되지 않은 경우 인덱스는 기본 테이블과 동일한 파일 그룹을 사용합니다. 파일 그룹은 이미 존재해야 합니다.
- "default"
  기본 파일 그룹에 지정된 인덱스를 만듭니다.
  
  이 컨텍스트에서 default는 키워드가 아니라 기본 파일 그룹에 대한 식별자이며 ON "default" 또는 ON **[default]**와 같이 구분되어야 합니다. "default"를 지정하면 현재 세션의 QUOTED_IDENTIFIER 옵션이 ON이어야 합니다. 이 값은 기본 설정입니다. 자세한 내용은 SET QUOTED_IDENTIFIER(Transact-SQL)를 참조하십시오.
- DROP_EXISTING
  명명된 기존 인덱스를 삭제하고 다시 작성하도록 지정합니다. 기본값은 OFF입니다.
  - ON
    기존 인덱스가 삭제되고 다시 작성됩니다. 지정된 인덱스 이름은 현재 존재하는 인덱스 이름과 같아야 합니다. 그러나 인덱스 정의는 수정할 수 있습니다. 예를 들어, 다른 열 또는 인덱스 옵션을 지정할 수 있습니다.
  - OFF
    지정된 인덱스 이름이 이미 존재하는 경우 오류가 표시됩니다. 인덱스 유형은 DROP_EXISTING을 사용하여 변경할 수 없습니다. 이전 버전과 호환되는 구문에서 WITH DROP_EXISTING은 WITH DROP_EXISTING = ON과 같습니다.
- MAXDOP = max_degree_of_parallelism
  인덱스 작업 중에 max degree of parallelism 서버 구성 옵션 구성 구성 옵션을 재정의합니다. MAXDOP를 사용하여 병렬 계획 실행에 사용되는 프로세서 수를 제한할 수 있습니다. 최대값은 64개입니다.
  
  max_degree_of_parallelism 값은 다음과 같을 수 있습니다.
  - 1 - 병렬 계획이 생성되지 않습니다.
  - >1 - 병렬 인덱스 작업에 사용되는 최대 프로세서 수를 현재 시스템 작업에 따라 지정된 수 또는 더 적은 수로 제한합니다. 예를 들어, MAXDOP = 4인 경우 사용되는 프로세서 수는 4개 이하가 됩니다.
  - 0(기본값) - 현재 시스템 작업에 따라 실제 프로세서 수 이하의 프로세서를 사용합니다.
  자세한 내용은 병렬 인덱스 작업 구성을 참조하십시오.
  
  참고
  
  병렬 인덱스 작업은 일부 Microsoft SQL Server 버전에서 사용할 수 있습니다.SQL Server 버전에서 지원하는 기능 목록을 보려면 SQL Server 2014 버전에서 지원하는 기능을 참조하십시오.

사용 권한

테이블에 대한 ALTER 권한이 필요합니다.

일반적인 주의

임시 테이블에 대한 columnstore 인덱스를 만들 수 있습니다. 테이블이 삭제되거나 세션이 종료되면 인덱스도 삭제됩니다.

columnstore 인덱스에 대해 지원되지 않는 데이터 형식의 열이 있는 경우 columnstore 인덱스에서 해당 열을 빼야 합니다.

제한 사항

비클러스터형 columnstore 인덱스:

최대 1,024개의 열만 사용할 수 있습니다.
비클러스터형 columnstore 인덱스가 있는 테이블은 UNIQUE 제약 조건, PRIMARY KEY 제약 조건 또는 FOREIGN KEY 제약 조건을 가질 수 있지만 비클러스터형 columnstore 인덱스에 제약 조건을 포함할 수 없습니다.
뷰 또는 인덱싱된 뷰에서는 만들 수 없습니다.
스파스 열을 포함할 수 없습니다.
ALTER INDEX 문을 사용하여 변경할 수 없습니다. 비클러스터형 인덱스를 변경하려면 인덱스를 삭제하고 해당 columnstore 인덱스를 대신 다시 만들어야 합니다. ALTER INDEX를 사용하여 columnstore 인덱스를 해제하고 다시 만들 수 있습니다.
INCLUDE 키워드를 사용하여 만들 수 없습니다.
인덱스를 정렬하기 위해 ASC 또는 DESC 키워드를 포함할 수 없습니다. columnstore 인덱스는 압축 알고리즘에 따라 정렬됩니다. 정렬을 사용하면 성능상의 많은 이점이 없어집니다.

columnstore 인덱스의 각 열은 다음 일반적인 비즈니스 데이터 형식 중 하나여야 합니다.

datetimeoffset [ ( n ) ]
datetime2 [ ( n ) ]
datetime
smalldatetime
date
time [ ( n ) ]
float [ ( n ) ]
real [ ( n ) ]
decimal [ ( precision [ , scale ] ) ]
money
smallmoney
bigint
int
smallint
tinyint
bit
nvarchar [ ( n ) ] nvarchar(max)는 지원되지 않음
nchar [ ( n ) ]
varchar [ ( n ) ]
char [ ( n ) ]
varbinary [ ( n ) ] varbinary(max)는 지원되지 않음
binary [ ( n ) ]

적용 대상: SQL Server(SQL Server 2014 - 현재 버전).

uniqueidentifier

다음 데이터 형식을 사용하는 열은 columnstore 인덱스에 포함할 수 없습니다.

ntext, text 및 image
varchar(max) 및 nvarchar(max)
rowversion(및 timestamp)
sql_variant
CLR 유형(hierarchyid 및 공간 형식)
xml

적용 대상: SQL Server 2012.

uniqueidentifier

columnstore 인덱스는 다음 기능과 함께 사용할 수 없습니다.

페이지 및 행 압축과 vardecimal 저장소 형식(columnstore 인덱스가 이미 다른 형식으로 압축되어 있음)
복제
변경 내용 추적
변경 데이터 캡처
Filestream

columnstore 인덱스의 성능상 이점 및 제한 사항에 대한 자세한 내용은 Columnstore 인덱스 소개를 참조하십시오.

메타데이터

columnstore 인덱스에 있는 모든 열이 메타데이터에 포괄 열로 저장됩니다. columnstore 인덱스에는 키 열이 없습니다. 이들 시스템 뷰는 columnstore 인덱스에 대한 정보를 제공합니다.

[ 맨 위 ]

예

1.간단한 비클러스터형 columnstore 인덱스 만들기

다음 예에서는 간단한 테이블 및 클러스터형 인덱스를 만든 다음 비클러스터형 columnstore 인덱스를 만드는 구문을 보여 줍니다.

CREATE TABLE SimpleTable
(ProductKey [int] NOT NULL, 
OrderDateKey [int] NOT NULL, 
DueDateKey [int] NOT NULL, 
ShipDateKey [int] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON SimpleTable (ProductKey);
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey);
GO

2.모든 옵션을 사용하여 단순 비클러스터형 인덱스 만들기

다음 예에서는 모든 옵션을 사용하여 비클러스터형 columnstore 인덱스를 만드는 구문을 보여 줍니다.

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON SimpleTable
(OrderDateKey, DueDateKey, ShipDateKey)
WITH (DROP_EXISTING =  ON, 
    MAXDOP = 2)
ON "default"
GO

분할된 테이블을 사용하는 전체 예를 보려면 Columnstore 인덱스 소개를 참조하십시오.

비클러스터형 Columnstore 인덱스의 데이터 변경

테이블에 비클러스터형 columnstore 인덱스를 만든 후에는 해당 테이블에서 데이터를 직접 수정할 수 없습니다. INSERT, UPDATE, DELETE 또는 MERGE를 사용한 쿼리는 실패하며 오류 메시지를 반환합니다. 테이블에서 데이터를 추가하거나 수정하려면 다음 중 하나를 수행합니다.

columnstore 인덱스를 사용하지 않도록 설정하거나 삭제합니다. 그런 다음 테이블에서 데이터를 업데이트할 수 있습니다. columnstore 인덱스를 사용하지 않도록 설정하는 경우 데이터 업데이트를 완료할 때 columnstore 인덱스를 다시 작성할 수 있습니다. 예를 들면 다음과 같습니다.
```
ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
-- update mytable --
ALTER INDEX mycolumnstoreindex on mytable REBUILD
```
columnstore 인덱스가 없는 준비 테이블에 데이터를 로드합니다. 준비 테이블에서 columnstore 인덱스를 작성합니다. 준비 테이블을 주 테이블의 빈 파티션으로 전환합니다.
columnstore 인덱스가 있는 테이블에서 빈 준비 테이블로 파티션을 전환합니다. 준비 테이블에 columnstore 인덱스가 있는 경우 columnstore 인덱스를 사용하지 않도록 설정합니다. 업데이트를 수행합니다. columnstore 인덱스를 작성 또는 다시 작성합니다. 준비 테이블을 주 테이블의 파티션(현재 비어 있음)으로 다시 전환합니다.