CREATE COLUMNSTORE INDEX (Transact-SQL)

  • Artigo

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstância Gerenciada de SQL do AzureAzure Synapse AnalyticsPDW (Analytics Platform System)

Converta uma tabela rowstore em um índice columnstore clusterizado ou crie um índice columnstore não clusterizado. Use um índice columnstore para executar análise operacional em tempo real com eficiência em uma carga de trabalho OLTP ou para melhorar o desempenho da consulta e a compactação de dados em cargas de trabalho de data warehouse.

Siga Novidades nos índices columnstore para obter as melhorias mais recentes para este recurso.

  • Os índices columnstore clusterizados ordenados foram introduzidos no SQL Server 2022 (16.x). Para obter mais informações, veja CRIAR ÍNDICE DE COLUMNSTORE.

  • Começando com o SQL Server 2016 (13.x), você pode criar a tabela como um índice columnstore clusterizado. Não é mais necessário criar uma tabela rowstore primeiro e, em seguida, convertê-la em um índice columnstore clusterizado.

  • Para obter informações sobre as diretrizes de design de índice columnstore, veja índices Columnstore – Diretrizes de design.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe do SQL Server e do Banco de Dados SQL do Azure:

-- Create a clustered columnstore index on disk-based table.
CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER (column [ , ...n ] ) ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

-- Create a nonclustered columnstore index on a disk-based table.
CREATE [ NONCLUSTERED ]  COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
        ( column  [ , ...n ] )
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]
    [ WITH ( <with_option> [ , ...n ] ) ]
    [ ON <on_option> ]
[ ; ]

<with_option> ::=
      DROP_EXISTING = { ON | OFF } -- default is OFF
    | MAXDOP = max_degree_of_parallelism
    | ONLINE = { ON | OFF }
    | COMPRESSION_DELAY  = { 0 | delay [ MINUTES ] }
    | DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
      [ ON PARTITIONS ( { partition_number_expression | range } [ , ...n ] ) ]

<on_option>::=
      partition_scheme_name ( column_name )
    | filegroup_name
    | "default"

<filter_expression> ::=
      column_name IN ( constant [ , ...n ]
    | column_name { IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !< } constant )

Sintaxe para o Azure Synapse Analytics, Parallel Data Warehouse, SQL Server 2022 (16.x) e versões posteriores:

CREATE CLUSTERED COLUMNSTORE INDEX index_name
    ON { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ORDER ( column [ , ...n ] ) ]
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] -- default is OFF
[;]

Observação

Para exibir a sintaxe do Transact-SQL para o SQL Server 2014 (12.x) e versões anteriores, confira a Documentação das versões anteriores.

Argumentos

Algumas das opções não estão disponíveis em todas as versões de mecanismo de banco de dados. A tabela a seguir mostra as versões quando as opções são introduzidas nos índices CLUSTERED COLUMNSTORE e NONCLUSTERED COLUMNSTORE:

Opção CLUSTERED NONCLUSTERED
COMPRESSION_DELAY SQL Server 2016 (13.x) SQL Server 2016 (13.x)
DATA_COMPRESSION SQL Server 2016 (13.x) SQL Server 2016 (13.x)
ONLINE SQL Server 2019 (15.x) SQL Server 2017 (14.x)
cláusula WHERE N/D SQL Server 2016 (13.x)

Todas as opções estão disponíveis no Banco de Dados SQL do Azure.

CREATE CLUSTERED COLUMNSTORE INDEX

Crie um índice columnstore clusterizado no qual todos os dados serão compactados e armazenados por coluna. O índice inclui todas as colunas na tabela e armazena toda a tabela. Se a tabela existente for um índice de heap ou clusterizado, ela será convertida em um índice columnstore clusterizado. Se a tabela já estiver armazenada como um índice columnstore clusterizado, o índice existente será descartado e recriado.

index_name

Especifica o nome do novo índice.

Se a tabela já tiver um índice columnstore clusterizado, você poderá especificar o mesmo nome que o do índice existente ou usar a opção DROP EXISTING para especificar um novo nome.

EM [ database_name. [ schema_name ]. | schema_name. ] table_name

Especifica o nome de uma, duas ou três partes da tabela a ser armazenada como um índice columnstore clusterizado. Se a tabela for um heap ou tiver um índice clusterizado, ela será convertida de rowstore para columnstore. Se a tabela já for um columnstore, essa instrução recompilará o índice columnstore clusterizado.

ORDER

Aplica-se a Azure Synapse Analytics,PDW (Analytics Platform System) e SQL Server 2022 (16.x) e versões posteriores

Use a column_store_order_ordinal coluna em sys.index_columns para determinar a ordem das colunas para um índice columnstore clusterizado. Isso ajuda na eliminação de segmento, especialmente com dados de cadeia de caracteres. Para obter mais informações, consulte Ajuste de desempenho com índice columnstore clusterizado ordenado e Índices columnstore - Diretrizes de design.

Para converter em um índice columnstore clusterizado ordenado, o índice existente deve ser um índice columnstore clusterizado. Use a opção DROP_EXISTING.

Os tipos de dados LOB (os tipos de dados de comprimento (máximo)) não podem ser a chave de um índice columnstore clusterizado ordenado.

Ao criar um índice columnstore clusterizado ordenado, use OPTION(MAXDOP = 1) para a classificação de maior qualidade com a instrução CREATE INDEX, em troca de uma duração significativamente maior da instrução CREATE INDEX. Para criar o índice o mais rápido possível, não limite MAXDOP e use todo o encadeamento paralelo que o servidor pode fornecer. A mais alta qualidade de compactação e classificação pode ajudar nas consultas no índice columnstore.

Quando um índice columnstore clusterizado ordenado é criado, as colunas de chave são indicadas pela coluna column_store_order_ordinal em sys.index_columns.

Opções WITH

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON especifica o descarte do índice existente e a criação de um novo índice columnstore.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (DROP_EXISTING = ON);

O padrão, DROP_EXISTING = OFF, espera que o nome do índice seja o mesmo que o nome existente. Ocorrerá um erro se o nome do índice especificado já existir.

MAXDOP = max_degree_of_parallelism

Esta opção pode substituir o grau máximo existente da configuração de servidor de paralelismo durante a 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 de max_degree_of_parallelism podem ser:

  • 1, que significa suprimir a geração de plano paralelo.
  • >1, que significa a restrição do número máximo de processadores usados em uma operação de índice paralela de acordo com o 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 é 4 ou menos.
  • 0 (padrão), que significa o uso do número real de processadores, ou menos, com base na carga de trabalho atual do sistema.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH (MAXDOP = 2);

Para obter mais informações, consulte Configurar o grau máximo de paralelismo (opção de configuração do servidor) e Configurar operações de índice paralelo.

COMPRESSION_DELAY = 0 | delay [ MINUTOS ]

Em uma tabela baseada em disco, o atraso especifica o número mínimo de minutos que um rowgroup delta no estado fechado precisa permanecer no rowgroup delta. O SQL Server pode compactá-lo no rowgroup compactado. Como as tabelas baseadas em disco não controlam os tempos de inserção e atualização em linhas individuais, o SQL Server aplica o atraso aos rowgroups delta no estado fechado.

O padrão é 0 minuto.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( COMPRESSION_DELAY = 10 MINUTES );

Para obter recomendações de quando usar COMPRESSION_DELAY, confira Introdução ao Columnstore para análise operacional em tempo real.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Especifica a opção de compactação de dados para a tabela, o número de partição ou o intervalo de partições especificado. As opções são as descritas a seguir:

  • COLUMNSTORE é o padrão e especifica compactar com a compactação de columnstore de melhor desempenho. Essa é a opção típica.
  • COLUMNSTORE_ARCHIVE compacta ainda mais a tabela ou a partição para um tamanho menor. Use esta opção para situações como arquivamento, que exigem um tamanho menor de armazenamento e podem dedicar mais tempo para armazenamento e recuperação.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( DATA_COMPRESSION = COLUMNSTORE_ARCHIVE );

Para obter mais informações sobre compactação, consulte Compactação de dados.

ONLINE = [ON | OFF]
  • ON especifica que o índice columnstore permaneça online e disponível enquanto a nova cópia do índice está sendo criada.
  • OFF especifica que o índice não fique disponível para ser usado enquanto a nova cópia estiver sendo criada.
CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
WITH ( ONLINE = ON );

Opções ON

Com essas opções, você consegue especificar opções de armazenamento de dados, como um esquema de partição, um grupo de arquivos específico ou o grupo de arquivos padrão. Se a opção ON não for especificada, o índice usará as configurações da partição ou do grupo de arquivos da tabela existente.

partition_scheme_name ( column_name ) especifica o esquema de partição para a tabela. O esquema de partição já deve existir no banco de dados. Para criar o esquema de partição, confira CREATE PARTITION SCHEME.

column_name especifica a coluna na qual um índice particionado será particionado. Essa coluna precisa corresponder ao tipo de dados, ao comprimento e à precisão do argumento da função de partição que partition_scheme_name está usando.

filegroup_name especifica o grupo de arquivos para armazenamento do índice columnstore clusterizado. Se nenhum local for especificado e a tabela não for particionada, o índice utilizará o mesmo grupo de arquivos que a exibição ou tabela subjacente. O grupo de arquivos já deve existir.

Para criar o índice no grupo de arquivos padrão, use "default" ou [default]. Se você especificar "default", a QUOTED_IDENTIFIER opção deve ser ON para a sessão atual. QUOTED_IDENTIFIER é ON por padrão. Para obter mais informações, confira SET QUOTED_IDENTIFIER (Transact-SQL).

CREATE [NONCLUSTERED] COLUMNSTORE INDEX

Criar um índice columnstore não clusterizado em uma tabela rowstore armazenado como um heap ou índice clusterizado. O índice pode ter uma condição filtrada e não precisa incluir todas as colunas da tabela subjacente. O índice columnstore requer espaço suficiente para armazenar uma cópia dos dados. Você pode atualizar o índice e ele é atualizado à medida que a tabela subjacente é alterada. O índice columnstore não clusterizado em um índice clusterizado permite a análise em tempo real.

index_name

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

( column [ ,...n ] )

Especifica as colunas a serem armazenadas. Um índice columnstore não clusterizado é limitado a 1.024 colunas. Cada coluna deve ser de um tipo de dados com suporte para índices columnstore. Confira Limitações e restrições para obter uma lista dos tipos de dados compatíveis.

EM [ database_name. [ schema_name ]. | schema_name. ] table_name

Especifica o nome de uma, duas ou três partes da tabela que contém o índice.

Opções WITH

DROP_EXISTING = [OFF] | ON

DROP_EXISTING = ON O índice existente é descartado 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.

DROP_EXISTING = OFF
Um erro será mostrado se o nome do í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 o grau máximo de paralelismo (opção de configuração do servidor) durante a 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 de max_degree_of_parallelism podem ser:

  • 1, que significa suprimir a geração de plano paralelo.
  • >1, que significa a restrição do número máximo de processadores usados em uma operação de índice paralela de acordo com o 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 é 4 ou menos.
  • 0 (padrão), que significa o uso do 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, confira Edições e recursos com suporte no SQL Server 2022.

ONLINE = [ON | OFF]
  • ON especifica que o índice columnstore permaneça online e disponível enquanto a nova cópia do índice está sendo criada.
  • OFF especifica que o índice não fique disponível para ser usado enquanto a nova cópia estiver sendo criada. Em um índice não clusterizado, a tabela base permanece disponível. Apenas o índice columnstore não clusterizado não é usado para atender às consultas até que o novo índice seja concluído.
CREATE COLUMNSTORE INDEX ncci ON Sales.OrderLines (StockItemID, Quantity, UnitPrice, TaxRate)
WITH ( ONLINE = ON );
COMPRESSION_DELAY = 0 | delay [ MINUTOS ]

Especifica um limite inferior para o tempo em que uma linha deve permanecer em um rowgroup delta antes que ela seja qualificada para migrar para um rowgroup compactado. Por exemplo, você pode dizer que se uma linha permanecer sem ser alterada durante 120 minutos, ela será elegível para a compactação em formato de armazenamento colunar.

Em um índice columnstore de tabelas baseadas em disco, não há controle da hora em que uma linha foi inserida ou atualizada. Em vez disso, o tempo fechado do rowgroup delta é usado como um proxy para a linha. A duração padrão é 0 minutos. Uma linha é migrada para o armazenamento colunar depois que 1 milhão de linhas se acumulam no grupo de linhas delta e é marcada como fechada.

DATA_COMPRESSION

Especifica a opção de compactação de dados para a tabela, o número de partição ou o intervalo de partições especificado. Aplica-se somente a índices columnstore, incluindo não clusterizados e clusterizados. As opções são as descritas a seguir:

  • COLUMNSTORE é o padrão e especifica compactar com a compactação de columnstore de melhor desempenho. Essa é a opção típica.
  • COLUMNSTORE_ARCHIVE compacta ainda mais a tabela ou a partição para um tamanho menor. Use essa opção para fins de arquivamento ou em outras situações que exijam tamanho do armazenamento menor e possam dispensar mais tempo para armazenamento e recuperação.

Para obter mais informações sobre compactação, consulte Compactação de dados.

WHERE <filter_expression> [ AND <filter_expression> ]

Chamado de predicado de filtro. Essa opção especifica quais linhas devem ser incluídas no índice. O SQL Server cria estatísticas filtradas nas linhas de dados no índice filtrado.

O predicado de filtro usa a lógica de comparação simples. Comparações que usam NULL literais não são permitidas com os operadores de comparação. Use os operadores IS NULL e IS NOT NULL, nesse caso.

Estes são alguns exemplos de predicados de filtro da tabela Production.BillOfMaterials:

  • WHERE StartDate > '20000101' AND EndDate <= '20000630'
  • WHERE ComponentID IN (533, 324, 753)
  • WHERE StartDate IN ('20000404', '20000905') AND EndDate IS NOT NULL

Para obter diretrizes sobre índices filtrados, confira Criar índices filtrados.

Opções ON

As opções a seguir especificam os grupos de arquivos nos quais o índice é criado.

partition_scheme_name ( column_name )

Especifica o esquema de partição que define os grupos de arquivos para os quais as partições de um índice particionado são mapeadas. O esquema de partição precisa existir no banco de dados por meio da execução de CREATE PARTITION SCHEME.

column_name especifica a coluna na qual um índice particionado será particionado. Essa coluna precisa corresponder ao tipo de dados, ao comprimento e à precisão do argumento da função de partição que partition_scheme_name está usando. column_name não é restrito às colunas na definição de índice. Ao particionar um índice columnstore, o Mecanismo de Banco de Dados adiciona a coluna de particionamento como uma coluna do índice, se ela ainda não estiver especificada.

Se a tabela estiver particionada e partition_scheme_name ou o grupo de arquivos não for especificado, 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 particionamento de índices, confira 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 estiver particionada, o índice usará o mesmo grupo de arquivos que a 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 precisa ser delimitado, como em ON "default" ou ON [default]. Se "default" for especificado, a opção QUOTED_IDENTIFIER deverá estar ON para a sessão atual, que é a configuração padrão. Para obter mais informações, confira SET QUOTED_IDENTIFIER (Transact-SQL).

Permissões

Exige a permissão ALTER na tabela.

Comentários

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

Índices filtrados

O índice filtrado é um índice não clusterizado otimizado, adequado a consultas que selecionam um pequeno percentual de linhas de uma tabela. Ele usa um predicado de filtro para indexar uma parte dos dados na tabela. Um índice filtrado bem projetado pode melhorar o desempenho da consulta, além de reduzir custos de armazenamento e de manutenção.

Opções SET necessárias para índices filtrados

As opções SET na coluna de valor necessário são necessárias sempre que ocorrer alguma das seguintes condições:

  • Você cria um índice filtrado.
  • Uma operação INSERT, UPDATE, DELETE ou MERGE modifica os dados de um índice filtrado.
  • O otimizador de consulta usa o índice filtrado para produzir o plano de consulta.
Opções Set Valor obrigatório Valor do servidor padrão Valor OLE DB e ODBC padrão Valor DB-Library padrão
ANSI_NULLS ATIVADO ATIVADO ATIVADO OFF
ANSI_PADDING ATIVADO ATIVADO ATIVADO OFF
ANSI_WARNINGS 1 ATIVADO ATIVADO ATIVADO OFF
ARITHABORT ATIVADO ATIVADO OFF OFF
CONCAT_NULL_YIELDS_NULL ATIVADO ATIVADO ATIVADO OFF
NUMERIC_ROUNDABORT OFF OFF OFF OFF
QUOTED_IDENTIFIER ATIVADO ATIVADO ATIVADO OFF

1 Definir ANSI_WARNINGS como ON define implicitamente ARITHABORT como ON quando o nível de compatibilidade do banco de dados é definido como 90 ou posterior. Se o nível de compatibilidade do banco de dados estiver definido como 80 ou inferior, você deverá definir explicitamente a opção ARITHABORT como ON.

Se as opções SET estiverem incorretas, as seguintes condições poderão ocorrer:

  • O índice filtrado não é criado.

  • O Mecanismo de Banco de Dados gera um erro e reverte as instruções INSERT, UPDATE, DELETE ou MERGE que alteram os dados no índice.

  • O otimizador de consulta não considera o índice no plano de execução para qualquer instrução Transact-SQL.

Para obter mais informações sobre índices filtrados, confira Criar índices filtrados.

Limitações e restrições

Cada coluna em um índice columnstore precisa ser de um dos seguintes tipos de dados de negócios comuns:

  • datetimeoffset [ ( n ) ]
  • datetime2 [ ( n ) ]
  • datetime
  • smalldatetime
  • date
  • time [ ( n ) ]
  • float [ ( n ) ]
  • real [ ( n ) ]
  • decimal [ ( precision [ , scale ] ) ]
  • numeric [ ( precision [ , scale ] ) ]
  • money
  • smallmoney
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • nvarchar [ ( n ) ]
  • nvarchar(max)1
  • nchar [ ( n ) ]
  • varchar [ ( n ) ]
  • varchar(max)1
  • char [ ( n ) ]
  • varbinary [ ( n ) ]
  • varbinary(max)1
  • binary [ ( n ) ]
  • uniqueidentifier2

1 Aplica-se ao SQL Server 2017 (14.x) e ao Banco de Dados SQL do Azure na camada de serviço Premium, na camada de serviço Standard (S3 e superior) e em todas as camadas de serviço de ofertas vCore, apenas em índices columnstore clusterizados.

2Aplica-se ao SQL Server 2014 (12.x) e versões posteriores.

Se a tabela subjacente tiver uma coluna de tipo de dados não compatível com índices columnstore, omita essa coluna do índice columnstore não clusterizado.

Dados LOB (Objeto Grande) maiores que 8 quilobytes são armazenados no armazenamento LOB fora de linha com apenas um ponteiro para o local físico armazenado no segmento de coluna. O tamanho dos dados armazenados não é relatado em sys.column_store_segments, sys.column_store_dictionaries nem sys.dm_db_column_store_row_group_physical_stats.

As colunas que usam um dos seguintes tipos de dados não podem ser incluídas em um índice columnstore:

  • ntext, text e image
  • nvarchar(max), varchar(max) e varbinary(max)1
  • rowversion (e timestamp)
  • sql_variant
  • Tipos de CLR (hierarchyid e tipos espaciais)
  • xml
  • uniqueidentifier2

1 Aplica-se ao SQL Server 2016 (13.x) e versões anteriores e índices columnstore não clusterizados.

2 Aplica-se ao SQL Server 2012 (11.x).

Índices columnstore não clusterizados:

  • Não pode ter mais de 1.024 colunas.
  • Não pode ser criado como um índice baseado em restrição. É possível ter restrições exclusivas, restrições de chave primária e restrições de chave estrangeira em uma tabela com um índice columnstore. As restrições sempre são impostas com um índice de repositório de linha. As restrições não podem ser impostas com um índice columnstore (clusterizado ou não clusterizado).
  • Não pode incluir uma coluna esparsa.
  • Não pode ser alterado usando a 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 recompilar um índice columnstore.
  • Ele não pode ser criado usando a palavra-chave INCLUDE.
  • Não é possível 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. No Azure Synapse Analytics e a partir do SQL Server 2022 (16.x), você pode especificar uma ordem para as colunas em um índice columnstore. Para obter mais informações, consulte Ajuste de desempenho com índice columnstore clusterizado ordenado.
  • Não é possível incluir colunas LOB do tipo nvarchar(max), varchar(max) e varbinary(max) em índices columnstore não clusterizados. Somente os índices columnstore clusterizados dão suporte a tipos de LOB, começando na versão do SQL Server 2017 (14.x), no Banco de Dados SQL do Azure (configurado na camada Premium, na camada Standard (S3 e superior) e em todas as camadas de ofertas vCore). As versões anteriores não oferecem suporte a tipos de LOB em índices columnstore clusterizados e não clusterizados.
  • Do SQL Server 2016 (13.x) em diante, é possível criar um índice columnstore não clusterizado em uma exibição indexada.

Os índices columnstore não podem ser combinados com os seguintes recursos:

  • Colunas computadas. A partir do SQL Server 2017 (14.x), um índice columnstore clusterizado pode conter uma coluna computada não persistente. No entanto, no SQL Server 2017 (14.x), os índices columnstore clusterizados não podem conter colunas computadas persistentes e você não pode criar índices não clusterizados em colunas computadas.
  • Compactação de página e linha e o formato de armazenamento vardecimal . (Um índice columnstore já é compactado em um formato diferente).
  • Replicação.
  • Fluxo de arquivos.

Não é possível usar cursores nem gatilhos em uma tabela com um índice columnstore clusterizado. Essa restrição não se aplica a índices columnstore não clusterizados. Você pode usar cursores e gatilhos em uma tabela com um índice columnstore clusterizado.

Limitações específicas do SQL Server 2014 (12.x):

As limitações a seguir se aplicam apenas ao SQL Server 2014 (12.x). Nesta versão, você pode usar índices columnstore clusterizados atualizáveis. Os índices columnstore não clusterizados ainda são somente leitura.

  • Controle de alterações. Você não pode usar o controle de alterações com índices columnstore.
  • Captura de dados de alterações. Esse recurso não pode ser habilitado em tabelas com um índice columnstore clusterizado. A partir do SQL Server 2016 (13.x), a captura de dados de alteração pode ser habilitada em tabelas com um índice columnstore não clusterizado.
  • Secundário legível. Não é possível acessar um CCI (índice columnstore clusterizado) por meio de uma réplica secundária para leitura de um grupo de disponibilidade Always On legível. É possível acessar um NCCI (índice columnstore não clusterizado) de um secundário legível.
  • MARS (conjunto de resultados ativos múltiplos). O SQL Server 2014 (12.x) usa esse recurso para conexões somente leitura com tabelas que contenham um índice columnstore. No entanto, o SQL Server 2014 (12.x) não é compatível com esse recurso em operações de DML (linguagem de manipulação de dados) simultâneas em uma tabela com um índice columnstore. Se você tentar usar o recurso para essa finalidade, o SQL Server encerrará as conexões e cancelará as transações.
  • Os índices columnstore não clusterizados não podem ser criados em uma exibição ou exibição indexada.

Para obter informações sobre os benefícios e limitações de desempenho dos índices columnstore, consulte Columnstore indexes: Overview (Índices columnstore: visão geral).

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. As seguintes exibições do sistema fornecem informações sobre índices columnstore:

Exemplos: converter tabela de rowstore em columnstore

a. Converter um índice columnstore clusterizado

Este exemplo cria uma tabela como um heap e, em seguida, converte-a em um índice columnstore clusterizado chamado cci_Simple. A criação do índice columnstore clusterizado altera o armazenamento de toda a tabela de rowstore para columnstore.

CREATE TABLE dbo.SimpleTable(
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cci_Simple ON dbo.SimpleTable;
GO

B. Converter um índice clusterizado em um índice columnstore clusterizado com o mesmo nome

Este exemplo cria uma tabela com um índice clusterizado e, em seguida, demonstra a sintaxe de conversão do índice clusterizado em índice columnstore clusterizado. A criação do índice columnstore clusterizado altera o armazenamento de toda a tabela de rowstore para columnstore.

CREATE TABLE dbo.SimpleTable2 (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable2 (ProductKey);
GO
CREATE CLUSTERED COLUMNSTORE INDEX cl_simple ON dbo.SimpleTable2
WITH (DROP_EXISTING = ON);
GO

C. Tratar índices não clusterizados ao converter uma tabela rowstore em um índice columnstore

Este exemplo mostra como tratar os índices não clusterizados ao converter uma tabela rowstore em um índice columnstore. A partir do SQL Server 2016 (13.x), não é necessária nenhuma ação especial. O SQL Server define e recria automaticamente os índices não clusterizados no novo índice columnstore clusterizado.

Se você quiser descartar os índices não clusterizados, use a instrução DROP INDEX antes de criar o índice columnstore. A opção DROP EXISTING remove somente o índice clusterizado que está sendo convertido. Ela não descarta os índices não clusterizados.

No SQL Server 2012 (11.x) e no SQL Server 2014 (12.x), não é possível criar um índice não clusterizado em um índice columnstore.

--Create the table for use with this example.
CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO
  
--Create two nonclustered indexes for use with this example
CREATE INDEX nc1_simple ON dbo.SimpleTable (OrderDateKey);
CREATE INDEX nc2_simple ON dbo.SimpleTable (DueDateKey);
GO

Somente para SQL Server 2012 (11.x) e SQL Server 2014 (12.x), você deve descartar os índices não clusterizados para criar o índice columnstore.

DROP INDEX dbo.SimpleTable.nc1_simple;
DROP INDEX dbo.SimpleTable.nc2_simple;
  
--Convert the rowstore table to a columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON dbo.SimpleTable;
GO

D. Converter uma tabela de fatos grande do rowstore em columnstore

Este exemplo explica como converter uma tabela de fatos grande de uma tabela rowstore em uma tabela columnstore.

  1. Crie uma pequena tabela para usar neste exemplo.

    --Create a rowstore table with a clustered index and a nonclustered index.
CREATE TABLE dbo.MyFactTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL
INDEX IDX_CL_MyFactTable CLUSTERED  ( ProductKey )
);

--Add a nonclustered index.
CREATE INDEX my_index ON dbo.MyFactTable ( ProductKey, OrderDateKey );

  2. Remova todos os índices não clusterizados da tabela rowstore. Pode ser interessante remover os índices por meio de script para recriá-los mais tarde.

    --Drop all nonclustered indexes
DROP INDEX my_index ON dbo.MyFactTable;

  3. Converta a tabela rowstore em uma tabela columnstore com um índice columnstore clusterizado.

    Primeiro, pesquise o nome do índice rowstore clusterizado existente. Na Etapa 1, definimos o nome do índice como IDX_CL_MyFactTable. Se o nome do índice não estava especificado, ele recebeu um nome de índice exclusivo gerado automaticamente. Você pode recuperar o nome gerado automaticamente com a seguinte consulta de exemplo:

    SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'MyFactTable';

    Opção 1: remover o índice clusterizado IDX_CL_MyFactTable existente e converter MyFactTable em columnstore. Alterar o nome do novo índice columnstore clusterizado.

    --Drop the clustered rowstore index.
DROP INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable;
GO
--Create a new clustered columnstore index with the name MyCCI.
CREATE CLUSTERED COLUMNSTORE
INDEX IDX_CCL_MyFactTable ON dbo.MyFactTable;
GO

    Opção 2: converter em columnstore e reutilizar o nome do índice clusterizado rowstore existente.

    --Create the clustered columnstore index,
--replacing the existing rowstore clustered index of the same name
CREATE CLUSTERED COLUMNSTORE
INDEX [IDX_CL_MyFactTable]
ON dbo.MyFactTable
WITH (DROP_EXISTING = ON);

E. Converter uma tabela columnstore em uma tabela rowstore com um índice clusterizado

Para converter uma tabela columnstore em uma tabela rowstore com um índice clusterizado, use a instrução CREATE INDEX com a opção DROP_EXISTING.

CREATE CLUSTERED INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable] ( ProductKey )
WITH ( DROP_EXISTING = ON );

F. Converter uma tabela columnstore em um heap rowstore

Para converter uma tabela columnstore em um heap rowstore, elimine o índice columnstore clusterizado. Normalmente, isso não é recomendado, mas pode ter alguns usos restritos. Para obter mais informações sobre heaps, confira Heaps (tabelas sem índices clusterizados).

DROP INDEX [IDX_CL_MyFactTable]
ON dbo.[MyFactTable];

G. Desfragmentar reorganizando o índice columnstore

Há duas maneiras de manter o índice columnstore clusterizado. Do SQL Server 2016 (13.x) em diante, use ALTER INDEX...REORGANIZE em vez de REBUILD. Para mais informações, consulte Rowgroup do índice columnstore. Em versões anteriores do SQL Server, você podia usar CREATE CLUSTERED COLUMNSTORE INDEX com DROP_EXISTING=ON ou ALTER INDEX (Transact-SQL) e a opção REBUILD. Ambos os métodos geraram os mesmos resultados.

Comece determinando o nome do índice columnstore clusterizado em MyFactTable.

SELECT i.object_id, i.name, t.object_id, t.name
FROM sys.indexes i
INNER JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED COLUMNSTORE'
AND t.name = 'MyFactTable';

Remova a fragmentação executando um REORGANIZE no índice columnstore.

--Rebuild the entire index by using ALTER INDEX and the REBUILD option.
ALTER INDEX IDX_CL_MyFactTable
ON dbo.[MyFactTable]
REORGANIZE;

Exemplos de índices columnstore não clusterizados

a. Criar um índice columnstore como um índice secundário em uma tabela rowstore

Este exemplo cria um índice columnstore não clusterizado em uma tabela rowstore. Somente um índice columnstore pode ser criado nesta situação. O índice columnstore exige armazenamento extra, pois contém uma cópia dos dados na tabela rowstore. Este exemplo cria uma tabela simples e um índice clusterizado rowstore e, em seguida, demonstra a sintaxe de criação de um índice columnstore não clusterizado.

CREATE TABLE dbo.SimpleTable (
    ProductKey [INT] NOT NULL,
    OrderDateKey [INT] NOT NULL,
    DueDateKey [INT] NOT NULL,
    ShipDateKey [INT] NOT NULL);
GO

CREATE CLUSTERED INDEX cl_simple ON dbo.SimpleTable (ProductKey);
GO

CREATE NONCLUSTERED COLUMNSTORE INDEX csindx_simple
ON dbo.SimpleTable (OrderDateKey, DueDateKey, ShipDateKey);
GO

B. Crie um índice columnstore não clusterizado básico usando todas as opções

O exemplo a seguir demonstra a sintaxe da criação de um índice columnstore não clusterizado no grupo de arquivos DEFAULT, especificando os graus máximos de paralelismo (MAXDOP) como 2.

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

C. Criar um índice columnstore não clusterizado com um predicado filtrado

O exemplo a seguir cria um índice columnstore não clusterizado filtrado na tabela Production.BillOfMaterials no banco de dados de exemplo AdventureWorks2022. O predicado do filtro pode incluir colunas que não sejam de chave no índice filtrado. O predicado neste exemplo seleciona apenas as linhas em que EndDate é não NULL.

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 COLUMNSTORE INDEX "FIBillOfMaterialsWithEndDate"
    ON Production.BillOfMaterials (ComponentID, StartDate)
    WHERE EndDate IS NOT NULL;

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

Aplica-se a: SQL Server 2012 (11.x) a SQL Server 2014 (12.x).

No SQL Server 2014 (12.x) e em versões anteriores, depois de criar um índice columnstore não clusterizado em uma tabela, não é possível modificar diretamente os dados nessa tabela. Uma consulta com INSERT, UPDATE, DELETE ou MERGE falhará e retornará uma mensagem de erro. Aqui estão as opções que você pode usar para adicionar ou modificar os dados na tabela:

  • 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 dbo.mytable DISABLE;
-- update the data in mytable as necessary
ALTER INDEX mycolumnstoreindex on dbo.mytable REBUILD;

  • Carregar dados em uma tabela de preparo que não tem 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.

Exemplos: Azure Synapse Analytics e Analytics Platform System (PDW)

a. Alterar um índice clusterizado para um índice columnstore clusterizado

Usando a instrução CREATE CLUSTERED COLUMNSTORE INDEX com DROP_EXISTING = ON, você pode:

  • Alterar um índice clusterizado para um índice columnstore clusterizado.

  • Recompilar um índice columnstore clusterizado.

Este exemplo cria a tabela xDimProduct como uma tabela rowstore com um índice clusterizado. Em seguida, o exemplo usa CREATE CLUSTERED COLUMNSTORE INDEX para alterar a tabela de rowstore para uma tabela columnstore.

-- Uses AdventureWorks
  
IF EXISTS (SELECT name FROM sys.tables
    WHERE name = N'xDimProduct'
    AND object_id = OBJECT_ID (N'xDimProduct'))
DROP TABLE xDimProduct;
  
--Create a distributed table with a clustered index.
CREATE TABLE xDimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey)
WITH ( DISTRIBUTION = HASH(ProductKey),
    CLUSTERED INDEX (ProductKey) )
AS SELECT ProductKey, ProductAlternateKey, ProductSubcategoryKey FROM DimProduct;

Pesquise o nome do índice clusterizado criado automaticamente para a nova tabela nos metadados do sistema, usando sys.indexes. Por exemplo:

SELECT i.object_id, i.name, t.object_id, t.name, i.type_desc
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
WHERE i.type_desc = 'CLUSTERED'
AND t.name = 'xdimProduct';

Agora, você pode optar por:

  1. Elimine o índice columnstore clusterizado existente com um nome criado automaticamente e, em seguida, crie um novo índice columnstore clusterizado com um nome definido pelo usuário.
  2. Remova e substitua o índice existente por um índice columnstore clusterizado, mantendo o mesmo nome gerado pelo sistema, como ClusteredIndex_1bd8af8797f7453182903cc68df48541.

Por exemplo:

--1. DROP the existing clustered columnstore index with an automatically-created name, for example:
DROP INDEX ClusteredIndex_1bd8af8797f7453182903cc68df48541 on xdimProduct;
GO
CREATE CLUSTERED COLUMNSTORE INDEX [<new_index_name>]
ON xdimProduct;
GO

--Or,
--2. Change the existing clustered index to a clustered columnstore index with the same name.
CREATE CLUSTERED COLUMNSTORE INDEX [ClusteredIndex_1bd8af8797f7453182903cc68df48541]
ON xdimProduct
WITH ( DROP_EXISTING = ON );
GO

B. Recompilar um índice columnstore clusterizado

Com base no exemplo anterior, este exemplo usa CREATE CLUSTERED COLUMNSTORE INDEX para recriar o índice columnstore clusterizado existente chamado cci_xDimProduct.

--Rebuild the existing clustered columnstore index.
CREATE CLUSTERED COLUMNSTORE INDEX cci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = ON );

C. Alterar o nome de um índice columnstore clusterizado

Para alterar o nome de um índice columnstore clusterizado, remova o índice columnstore clusterizado existente e, em seguida, recrie o índice com um novo nome.

Recomendamos que você limite essa operação a uma tabela pequena ou vazia. Demora muito para remover um índice columnstore clusterizado grande e recriá-lo com um nome diferente.

Este exemplo faz referência ao índice columnstore clusterizado cci_xDimProduct do exemplo anterior. Este exemplo descarta o índice columnstore clusterizado cci_xDimProduct e, em seguida, recria o índice columnstore clusterizado com o nome mycci_xDimProduct.

--For illustration purposes, drop the clustered columnstore index.
--The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xDimProduct;
  
--Create a clustered index with a new name, mycci_xDimProduct.
CREATE CLUSTERED COLUMNSTORE INDEX mycci_xDimProduct
ON xdimProduct
WITH ( DROP_EXISTING = OFF );

D. Converter uma tabela columnstore em uma tabela rowstore com um índice clusterizado

É possível que haja uma situação em que você queira remover um índice columnstore clusterizado e criar um índice clusterizado. Quando você remove um índice columnstore clusterizado, a tabela é alterada para o formato rowstore. Este exemplo converte uma tabela columnstore em uma tabela rowstore com um índice clusterizado com o mesmo nome. Nenhum dos dados é perdido. Todos os dados vão para a tabela de armazenamento de linhas e as colunas listadas se tornam as colunas-chave no índice clusterizado.

--Drop the clustered columnstore index and create a clustered rowstore index.
--All of the columns are stored in the rowstore clustered index.
--The columns listed are the included columns in the index.
CREATE CLUSTERED INDEX cci_xDimProduct
ON xdimProduct (ProductKey, ProductAlternateKey, ProductSubcategoryKey, WeightUnitMeasureCode)
WITH ( DROP_EXISTING = ON);

E. Converter uma tabela columnstore novamente em um heap rowstore

Use DROP INDEX (SQL Server PDW) para remover o índice columnstore clusterizado e converter a tabela em um heap rowstore. Este exemplo converte a tabela cci_xDimProduct em um heap rowstore. A tabela continua a ser distribuída, mas é armazenada como um heap.

--Drop the clustered columnstore index. The table continues to be distributed, but changes to a heap.
DROP INDEX cci_xdimProduct ON xdimProduct;

F. Criar um índice columnstore clusterizado ordenado na tabela sem índice

Um índice columnstore não ordenado abrange todas as colunas por padrão sem precisar especificar uma lista de colunas. Um índice columnstore ordenado permite especificar a ordem das colunas. A lista não precisa incluir todas as colunas.

Os índices columnstore ordenados estão disponíveis em Azure Synapse Analytics, PDW (Analytics Platform System) e SQL Server 2022 (16.x). Para obter mais informações, confira Ajuste de desempenho com índice columnstore clusterizado ordenado.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE);

G. Converter um índice columnstore clusterizado em um índice columnstore clusterizado com ordenado

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE)
WITH (DROP_EXISTING = ON);

H. Adicionar uma coluna à ordenação de um índice columnstore clusterizado ordenado

No Azure Synapse Analytics, no PDW (Analytics Platform System) e a partir do SQL Server 2022 (16.x), você pode especificar uma ordem para as colunas em um índice columnstore. O índice columnstore clusterizado ordenado original foi ordenado somente na coluna SHIPDATE. O exemplo a seguir adiciona a coluna PRODUCTKEY à ordenação.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (SHIPDATE, PRODUCTKEY)
WITH (DROP_EXISTING = ON);

I. Alterar o ordinal de colunas ordenadas

O índice columnstore clusterizado original ordenado foi ordenado em SHIPDATE, PRODUCTKEY. O exemplo a seguir altera a ordenação para PRODUCTKEY, SHIPDATE.

CREATE CLUSTERED COLUMNSTORE INDEX cci ON Sales.OrderLines
ORDER (PRODUCTKEY,SHIPDATE)
WITH (DROP_EXISTING = ON);

J. Criar um índice columnstore clusterizado ordenado

Aplica-se: Azure Synapse Analytics e SQL Server 2022 (16.x)

Você pode criar um índice columnstore clusterizado com chaves de ordenação. Ao criar um índice columnstore clusterizado ordenado, você deve aplicar a dica MAXDOP = 1 de consulta para obter a máxima qualidade de classificação e a menor duração.

CREATE CLUSTERED COLUMNSTORE INDEX [OrderedCCI] ON dbo.FactResellerSalesPartCategoryFull
ORDER (EnglishProductSubcategoryName, EnglishProductName)
WITH (MAXDOP = 1, DROP_EXISTING = ON);

Conteúdo relacionado