CREATE COLUMNSTORE INDEX (Transact-SQL)

Artigo
06/18/2013

Cria um índice columnstore em uma tabela especificada. Um índice columnstore xVelocity de memória otimizada é um tipo de índice não clusterizado compactado. Há um limite de um índice columnstore por tabela. Um índice pode ser criado antes que haja dados na tabela. Uma tabela com um índice columnstore não pode ser atualizada. Para obter mais informações sobre como usar índices columnstore, consulte Índices columnstore.

Observação
Para obter informações sobre como criar um índice relacional, consulte CREATE INDEX (Transact-SQL). Para obter informações sobre como criar um índice XML, consulte CREATE XML INDEX (Transact-SQL). Para obter informações sobre como criar um índice espacial, consulte CREATE SPATIAL INDEX (Transact-SQL).

Ícone de vínculo de tópico Convenções de sintaxe Transact-SQL

Sintaxe

CREATE [ NONCLUSTERED ] COLUMNSTORE INDEX index_name 
    ON <object> ( column  [ ,...n ] )
    [ WITH ( <column_index_option> [ ,...n ] ) ]
    [ ON {
           { partition_scheme_name ( column_name ) } 
           | filegroup_name 
           | "default" 
         }
    ]
[ ; ]

<object> ::=
{
    [database_name. [schema_name ] . | schema_name . ]
     table_name
{

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

Argumentos

NONCLUSTERED
Cria um índice columnstore que especifica a ordenação lógica de uma tabela. Índices columnstore clusterizados não têm suporte.
COLUMNSTORE
Indica que o índice será um índice columnstore.
index_name
É o nome do índice. Os nomes de índice devem ser exclusivos em uma tabela ou exibição, mas não precisam ser exclusivos no banco de dados. Nomes de índice devem seguir as regras de identificadores.
column
É a coluna, ou colunas, em que o índice se baseia. Um índice columnstore é limitado a 1024 colunas.
ON partition_scheme_name**(column_name)**
Especifica o esquema de partição que define os grupos de arquivos nos quais as partições de um índice particionado serão mapeadas. O esquema de partição deve existir no banco de dados com a execução de CREATE PARTITION SCHEME. column_name especifica a coluna na qual um índice particionado será particionado. Essa coluna deve corresponder ao tipo, ao comprimento e à precisão dos dados do argumento da função de partição que partition_scheme_name está usando. column_name não é restrito às colunas na do índice. Ao particionar um índice columnstore, o Mecanismo de Banco de Dados adiciona a coluna de particionamento como uma coluna do índice, se ela já não estiver especificada.

Se partition_scheme_name ou filegroup não for especificado e a tabela for particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento que a tabela subjacente.

Para obter mais informações sobre como particionar índices, consulte Tabelas e índices particionados.
ON filegroup_name
Cria o índice especificado no grupo de arquivos especificado. Se nenhum local for especificado e a tabela ou exibição não for particionada, o índice usará o mesmo grupo de arquivos que a tabela ou exibição subjacente. O grupo de arquivos já deve existir.
ON "default"
Cria o índice especificado no grupo de arquivos padrão.

Nesse contexto, default não é uma palavra-chave. É um identificador para o grupo de arquivos padrão e deve ser delimitado, como em ON "default" ou ON [default]. Se "padrão" for especificado, a opção QUOTED_IDENTIFIER deverá ser definida como ON para a sessão atual. Esta é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

<object>::=

É o objeto totalmente qualificado ou não totalmente qualificado a ser indexado.

database_name
É o nome do banco de dados.
schema_name
É o nome do esquema ao qual a tabela pertence.
table_name
É o nome da tabela a ser indexada.

<column_index_option>::=

Especifica as opções a serem usadas ao criar o índice de repositório de coluna.

DROP_EXISTING
Especifica que o índice nomeado preexistente deve ser removido e recriado. O padrão é OFF.
- ON
  O índice existente é removido e recriado. O nome de índice especificado deve ser igual ao índice existente atualmente; no entanto, a definição de índice pode ser modificada. Por exemplo, você pode especificar colunas ou opções de índice diferentes.
- OFF
  Um erro será exibido se o nome de índice especificado já existir. O tipo de índice não pode ser alterado com DROP_EXISTING. Na sintaxe compatível com versões anteriores, WITH DROP_EXISTING é equivalente a WITH DROP_EXISTING = ON.

MAXDOP = max_degree_of_parallelism
Substitui a opção de configuração Configurar a opção de configuração de servidor max degree of parallelism para a duração da operação de índice. Use MAXDOP para limitar o número de processadores usados em uma execução de plano paralelo. O máximo é de 64 processadores.

max_degree_of_parallelism pode ser:

1
Suprime a geração de plano paralelo.
>1
Restringe o número máximo de processadores usados em uma operação de índice paralela ao número especificado, ou menos, com base na carga de trabalho atual do sistema.
0 (padrão)
Usa o número real de processadores, ou menos, com base na carga de trabalho atual do sistema.

Para obter mais informações, consulte Configurar operações de índice paralelo.

Observação
As operações de índice paralelas não estão disponíveis em todas as edições do Microsoft SQL Server. Para obter uma lista de recursos com suporte nas edições do SQL Server, consulte Recursos compatíveis com as edições do SQL Server 2012.

Comentários

Os índices podem ser criados em uma tabela temporária. Quando a tabela for removida ou a sessão encerrada, os índices serão removidos.

Os tipos de dados corporativos comuns podem ser incluídos em um índice columnstore. Os tipos de dados a seguir podem ser incluídos em um índice columnstore.

char e varchar
nchar e nvarchar (exceto varchar(max) e nvarchar(max))
decimal (e numeric) (exceto com precisão maior que 18 dígitos.)
int, bigint, smallint e tinyint
float (e real)
bit
money e smallmoney
Todos os tipos de dados de data e hora (exceto datetimeoffset com escala maior que 2)

Os tipos de dados a seguir não podem ser incluídos em um índice columnstore.

binary e varbinary
ntext, text e image
varchar(max) e nvarchar(max)
uniqueidentifier
rowversion (e timestamp)
sql_variant
decimal (e numeric) com precisão maior que 18 dígitos
datetimeoffset com escala maior que 2
Tipos CLR (hierarchyid e tipos espaciais)
xml

Restrições básicas

Um índice columnstore:

Não pode ter mais de 1024 colunas.
Não pode ser clusterizado. Apenas índices columnstore não clusterizados estão disponíveis.
Não pode ser um índice exclusivo.
Não pode ser criado em uma exibição ou exibição indexada.
Não pode incluir uma coluna esparsa.
Não pode atuar como uma chave primária ou estrangeira.
Não pode ser alterado por meio da instrução ALTER INDEX. Em vez disso, remova e recrie o índice columnstore. (Você pode usar ALTER INDEX para desabilitar e recriar um índice columnstore.)
Não pode ser criado por meio da palavra-chave INCLUDE.
Não pode incluir as palavras-chave ASC ou DESC para classificar o índice. Os índices columnstore são ordenados de acordo com os algoritmos de compactação. A classificação eliminará muitos dos benefícios de desempenho.

Índices columnstore não podem ser combinados com os recursos a seguir:

Compactação de página e de linha, e formato de armazenamento vardecimal (um índice columnstore já foi compactado em um formato diferente).
Replicação
Controle de alterações
Change Data Capture
Fluxo de arquivos

Para obter informações sobre os benefícios de desempenho e as limitações de índices columnstore, consulte Índices columnstore.

Permissões

Requer a permissão ALTER na tabela.

Exemplos

A.Criando um índice não clusterizado simples

O exemplo a seguir cria uma tabela simples e um índice clusterizado e, em seguida, demonstra a sintaxe de criação de um índice 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

B.Criando um índice não clusterizado simples com o uso de todas as opções

O exemplo a seguir cria uma tabela simples e um índice clusterizado e, em seguida, demonstra a sintaxe de criação de um índice columnstore.

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

Para obter um exemplo mais complexo que usa tabelas particionadas, consulte Índices columnstore.