CREATE COLUMNSTORE INDEX (Transact-SQL)

Cria um índice columnstore na memória não clusterizado em uma tabela do SQL Server. Use um índice columnstore não clusterizado para se beneficiar da compactação columnstore de modo a melhorar significativamente os tempos de execução de consulta em dados somente leitura.

Para criar um índice columnstore clusterizado, consulte CREATE CLUSTERED COLUMNSTORE INDEX (Transact-SQL).

Para obter mais informações, consulte estes tópicos:

• Índices columnstore descritos

• Usando índices columnstore não clusterizados

• Usando índices columnstore clusterizados

Aplica-se a: SQL Server (do SQL Server 2012 à versão atual). )

Convenções da sintaxe Transact-SQL

Sintaxe

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
 }

Argumentos

• index_name
  Especifica o nome do índice. index_name deve ser exclusivo na tabela, mas não precisa ser exclusivo no banco de dados. Os nomes de índice devem seguir as regras de identificadores.

  Para um índice columnstore não clusterizado,

• ( column [ ,...n ] )
  Especifica as colunas a serem armazenadas. Um índice columnstore não clusterizado é limitado a 1024 colunas.

  Cada coluna deve ser de um tipo de dados com suporte para índices columnstore. Consulte Limitações e restriçõespara obter uma lista dos tipos de dados com suporte.

• ON [database_name. [schema_name ] . | schema_name . ] table_name
  Especifica o nome de uma, duas ou três partes da tabela que conterá o índice.

• ON
  Essas opções especificam os grupos de arquivos nos quais o índice será criado.

  • 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.

    Um índice columnstore em uma tabela particionada deve ser alinhado por partição.

    Para obter mais informações sobre como particionar índices, consulte Tabelas e índices particionados.

  • filegroup_name
    Especifica o nome do grupo de arquivos no qual criar o índice. Se filegroup_name não for especificado e a tabela não for particionada, o índice utilizará o mesmo grupo de arquivos da tabela subjacente. O grupo de arquivos já deve existir.

  • "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 "default" for especificado, a opção QUOTED_IDENTIFIER deverá ser definida como ON para a sessão atual. Essa é a configuração padrão. Para obter mais informações, consulte SET QUOTED_IDENTIFIER (Transact-SQL).

  • 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.

    Os valores max_degree_of_parallelism podem ser:

    • 1 - Suprima 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. Por exemplo, quando MAXDOP = 4, o número de processadores usados será 4 ou menos.

    • 0 (padrão) - Use 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.

    Dica

    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 2014.

Permissões

Exige a permissão ALTER na tabela.

Comentários gerais

Um índice columnstore pode ser criado em uma tabela temporária. Quando a tabela for removida ou a sessão encerrada, o índice também será removido.

Se você tiver uma coluna com um tipo de dados sem suporte para índices columnstore, será necessário omitir essa coluna do índice columnstore.

Limitações e restrições

Índices columnstore não clusterizados:

• Não pode ter mais de 1024 colunas.

• Uma tabela com um índice columnstore não clusterizado pode ter restrições exclusivas, restrições de chave primária ou restrições de chave estrangeira, mas as restrições não podem ser incluídas no índice columnstore não clusterizado.

• Não pode ser criado em uma exibição ou exibição indexada.

• Não pode incluir uma coluna esparsa.

• Não pode ser alterado por meio da instrução ALTER INDEX. Para alterar o índice não clusterizado, é preciso descartar e recriar 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.

Cada coluna em um índice columnstore deve ser de um dos seguintes tipos de dados corporativos comuns.

• 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 ) ] exceto nvarchar (max) não tem suporte

• nchar [ ( n ) ]

• varchar [ ( n ) ]

• char [ ( n ) ]

• varbinary [ ( n ) ] exceto varbinary (max) não tem suporte

• binary [ ( n ) ]

Aplica-se a: SQL Server (do SQL Server 2014 à versão atual).

• uniqueidentifier

As colunas que usam qualquer um dos tipos de dados a seguir não podem ser incluídas em um índice columnstore.

• ntext, texto e imagem

• varchar (max) e nvarchar (max)

• rowversion (e carimbo de data/hora)

• sql_variant

• Tipos CLR (hierarchyid e tipos espaciais)

• xml

Aplica-se a: SQL Server 2012.

• uniqueidentifier

Í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 descritos.

Metadados

Todas as colunas em um índice columnstore são armazenadas nos metadados como colunas incluídas. O índice columnstore não tem colunas de chave. Essas exibições do sistema fornecem informações sobre índices columnstore.

• sys.indexes (Transact-SQL)

• sys.index_columns (Transact-SQL)

• sys.partitions (Transact-SQL)

• sys.column_store_segments (Transact-SQL)

• sys.column_store_dictionaries (Transact-SQL)

• sys.column_store_row_groups (Transact-SQL)

[ INÍCIO ]

Exemplos

A.Criar um índice columnstore não clusterizado simples

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

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.Criar um índice não clusterizado simples com o uso de todas as opções

O exemplo a seguir demonstra a sintaxe de criação de um índice columnstore não clusterizado usando todas as opções.

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 descritos.

Alterar os dados em um índice columnstore não clusterizado

Quando você cria um índice columnstore não clusterizado em uma tabela, não pode modificar diretamente os dados nessa tabela. Uma consulta com INSERT, UPDATE, DELETE ou MERGE falhará e retornará uma mensagem de erro. Para adicionar ou modificar os dados na tabela, siga um destes procedimentos:

• Desabilitar ou descartar o índice columnstore. Depois, você pode atualizar os dados na tabela. Se você desabilitar o índice columnstore, poderá recriar o índice columnstore quando concluir a atualização dos dados. Por exemplo,

  ALTER INDEX mycolumnstoreindex ON mytable DISABLE;
  -- update mytable --
  ALTER INDEX mycolumnstoreindex on mytable REBUILD
  

• Carregar dados em uma tabela de preparação sem um índice columnstore. Criar um índice columnstore na tabela de preparo. Alternar a tabela de preparo para uma partição vazia da tabela principal.

• Alternar uma partição da tabela com o índice columnstore para uma tabela de preparo vazia. Se houver um índice columnstore na tabela de preparo, desabilite o índice columnstore. Executar quaisquer atualizações. Criar (ou recriar) o índice columnstore. Alternar a tabela de preparo para a partição anterior (não vazia) da tabela principal.

[ INÍCIO ]