CREATE COLUMNSTORE INDEX (Transact-SQL)

 

Publicado: dezembro de 2016

ESTE TÓPICO APLICA-SE A:simSQL Server (a partir do 2012)simBanco de Dados SQL do AzuresimAzure SQL Data Warehouse simParallel Data Warehouse

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

System_CAPS_ICON_note.jpg Observação


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

Vá para exemplos:

Vá para cenários:

Saiba Mais:

Topic link icon Convenções de sintaxe Transact-SQL

-- Syntax for SQL Server and Azure SQL Database  
  
-- Create a clustered columnstore index on disk-based table.  
CREATE CLUSTERED COLUMNSTORE INDEX index_name  
    ON [database_name. [schema_name ] . | schema_name . ] table_name  
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ]  
[ ; ]  
  
--Create a non-clustered columnstore index on a disk-based table.  
CREATE [NONCLUSTERED]  COLUMNSTORE INDEX index_name   
    ON [database_name. [schema_name ] . | schema_name . ] table_name   
        ( column  [ ,...n ] )  
    [ WITH ( < with_option> [ ,...n ] ) ]  
    [ ON <on_option> ]  
    [ WHERE <filter_expression> [ AND <filter_expression> ] ]  
[ ; ]  
  
<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  
  

-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
CREATE CLUSTERED COLUMNSTORE INDEX index_name   
    ON [ database_name . [ schema_name ] . | schema_name . ] table_name  
    [ WITH ( DROP_EXISTING = { ON | OFF } ) ] --default is OFF  
[;]  

CREATE CLUSTERED COLUMNSTORE INDEX
Crie um índice columnstore clusterizado no qual todos os dados sã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 heap ou índice clusterizado, a tabela será convertida em um índice columnstore clusterizado. Se a tabela já estiver armazenada como um índice columnstore clusterizado, o índice existente será removido e recriado.

index_name
Especifica o nome para o novo índice.

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

ON [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 índice clusterizado de tabela será convertido do rowstore em columnstore. Se a tabela já está columnstore, essa instrução reconstruirá o índice columnstore clusterizado.

com
DROP_EXISTING = [DESATIVADO] | ON
DROP_EXISTING = ON especifica para descartar o índice columnstore clusterizado existente e criar um novo índice columnstore.

O padrão, DROP_EXISTING = OFF espera que o nome do índice é o mesmo que o nome existente. Um erro ocorrerá se o nome do índice especificado já existe.

MAXDOP = max_degree_of_parallelism
Substitui a configuração de servidor degrau máximo de paralelismo existente enquanto durar a operação do í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 os valores 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 o grau máximo de paralelismo opção de configuração do servidor, e configurar operações de índice paralelas.

COMPRESSION_DELAY = 0 | atraso [minutos]
Este recurso estará disponível após o SQL Server 2016 CTP 3.3

Para uma tabela baseada em disco, atraso Especifica o número mínimo de minutos que um grupo de linhas delta no estado fechado deve permanecer no rowgroup delta antes do SQL Server pode compactá-lo no rowgroup compactado. Uma vez que as tabelas baseadas em disco não rastrear inserir e atualizar horários em linhas individuais, SQL Server aplica o atraso para rowgroups delta no estado fechado.

O padrão é 0 minutos.

Para obter recomendações sobre quando usar COMPRESSION_DELAY, consulte começar Columnstore para análise em tempo real operacional.

DATA_COMPRESSION = COLUMNSTORE | COLUMNSTORE_ARCHIVE

Aplica-se a: do SQL Server 2016 ao SQL Server 2016.

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 seguintes:

COLUMNSTORE
É o padrão e especifica para compactar com a maior compactação columnstore de alto desempenho. Essa é a opção típica.

COLUMNSTORE_ARCHIVE
COLUMNSTORE_ARCHIVE compactará ainda mais a tabela ou partição para um tamanho menor. Use esta opção para situações como arquivamento que exijam um tamanho menor de armazenamento e possam dispensar mais tempo para armazenamento e recuperação.

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

ON
Com as opções ON, você pode 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 estiver especificada, o índice usará a partição de configurações ou as configurações de grupo de arquivos da tabela existente.

partition_scheme_name ( column_name )
Especifica o esquema de partição da tabela. O esquema de partição já deve existir no banco de dados. Para criar o esquema de partição, consulte CREATE PARTITION SCHEME.

nome da coluna Especifica a coluna na qual um índice particionado será particionado. Esta coluna deve corresponder ao tipo de dados, comprimento e precisão do argumento da partição de funçã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 da exibição ou tabela subjacente. O grupo de arquivos já deve existir.

"default"
Para criar o índice no grupo de arquivos padrão, use "default" ou [ default ].

Se "padrão" for especificado, a opção QUOTED_IDENTIFIER deverá ser definida como ON para a sessão atual. QUOTED_IDENTIFIER está ON por padrão. Para obter mais informações, veja SET QUOTED_IDENTIFIER (Transact-SQL).

CRIAR [NONCLUSTERED ] ÍNDICE COLUMNSTORE
Criar um índice columnstore não clusterizado na memória 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 exige espaço suficiente para armazenar uma cópia dos dados. Ele pode ser atualizado e será atualizado conforme a tabela subjacente é alterada. O índice não clusterizado columnstore em um índice clusterizado habilita a análise em tempo real.

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

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

Cada coluna deve ser de um tipo de dados com suporte para índices columnstore. Consulte limitações e restrições para 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.

com

DROP_EXISTING = [DESATIVADO] | ON
DROP_EXISTING = ON, o índice existente será 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á exibido se o nome do índice especificado já existe. 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 o configurar o grau máximo de paralelismo opção de configuração de servidor opção de configuração 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 os valores 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.

System_CAPS_ICON_note.jpg Observação


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 que têm suporte nas edições do SQL Server, consulte edições e recursos com suporte para SQL Server 2016.

ONLINE = [ON | OFF]

Aplica-se a: SQL Server vNext, em apenas índices columnstore não clusterizado.

ON Especifica que o índice não clusterizado columnstore permanecerá online e disponível enquanto a nova cópia do índice está sendo criado.

Desativar Especifica que o índice não estará disponível para uso enquanto a nova cópia está sendo construída. Como esse é apenas um índice não clusterizado, a tabela base permanecerão disponível, apenas o índice columnstore não clusterizado não será usado para atender a consultas até que o novo índice seja concluído.

COMPRESSION_DELAY = 0 | <> </> >[Minutos]
Este recurso estará disponível após o SQL Server 2016 CTP 3.3

Especifica um limite inferior em quanto tempo uma linha deve permanecer no rowgroup delta antes que ela seja qualificada para migração para o rowgroup compactado. Por exemplo, um cliente pode dizer que se uma linha for alterada de 120 minutos, torná-la elegível para a compactação em formato de armazenamento Colunar. Índice de columnstore em tabelas baseadas em disco, nós não controlar o tempo quando uma linha foi inserida ou atualizada, usamos o delta rowgroup fechado tempo como um proxy para a linha em vez disso. A duração padrão é 0 minutos. Uma linha é migrada para o armazenamento Colunar depois que foram acumulado 1 milhão de linhas no rowgroup delta e ele foi marcado como fechado.

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. As opções são as seguintes:

COLUMNSTORE

Aplica-se a: do SQL Server 2016 ao SQL Server 2016.

Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados. É o padrão e especifica para compactar com a maior compactação columnstore de alto desempenho. Essa é a opção típica.

COLUMNSTORE_ARCHIVE

Aplica-se a: do SQL Server 2016 ao SQL Server 2016.

Aplica-se somente a índices columnstore, incluindo índices columnstore não clusterizados e clusterizados. COLUMNSTORE_ARCHIVE compactará ainda mais a tabela ou partição para um tamanho menor. Isso pode ser usado para fins de arquivamento, ou em outras situações que exijam menos armazenamento e possam dispensar mais tempo para armazenamento e recuperação.

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

ONDE <filter_expression>[AND <filter_expression>]</filter_expression> </filter_expression>

Aplica-se a: do SQL Server 2016 ao SQL Server 2016.

Chamado um predicado de filtro, especifica quais linhas devem ser incluídas no índice. SQL Servercria estatísticas filtradas nas linhas de dados no índice filtrado.

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

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 orientação sobre índices filtrados, consulte criar índices filtrados.

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 executando CREATE PARTITION SCHEME. nome da coluna Especifica a coluna na qual um índice particionado será particionado. Esta coluna deve corresponder ao tipo de dados, comprimento e precisão do argumento da partição de função que partition_scheme_name está usando. nome da coluna não é restrito às colunas na definição 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 arquivos não for especificado e a tabela esteja particionada, o índice será colocado no mesmo esquema de partição, usando a mesma coluna de particionamento da tabela subjacente.

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

Para obter mais informações sobre o particionamento de í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 esteja particionada, o índice usa 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. Ele é um identificador para o grupo de arquivos padrão e deve ser delimitado, como em ON "padrão" ou ON [padrão]. Se "padrão" 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, veja SET QUOTED_IDENTIFIER (Transact-SQL).

Exige a permissão ALTER na tabela.

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.

Índice filtrado é um índice não clusterizado otimizado, adequado a consultas que selecionam uma pequena porcentagem 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 Valor necessário são necessárias sempre que ocorrer alguma das seguintes condições:

  • Criar um índice filtrado.

  • A operação INSERT, UPDATE, DELETE ou MERGE modificar os dados de um índice filtrado.

  • O índice filtrado é usado pelo otimizador de consulta para produzir o plano de consulta.

    opções SETValor ObrigatórioValor do servidor padrãoPadrão

    Valor OLE DB e ODBC
    Padrão

    Valor da DB-Library
    ANSI_NULLSONONONOFF
    ANSI_PADDINGONONONOFF
    ANSI_WARNINGS*ONONONOFF
    ARITHABORTONONOFFOFF
    CONCAT_NULL_YIELDS_NULLONONONOFF
    NUMERIC_ROUNDABORTOFFOFFOFFOFF
    QUOTED_IDENTIFIERONONONOFF

    *A definição de ANSI_WARNINGS como ON definirá ARITHABORT implicitamente como ON quando o nível de compatibilidade do banco de dados for definido como 90 ou mais. Se o nível de compatibilidade do banco de dados estiver definido como 80 ou menos, a opção ARITHABORT deverá ser definida explicitamente 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, consulte criar índices filtrados.

Se a tabela base tem uma coluna de um tipo de dados que não há suporte para índices columnstore, você deve omitir a coluna do índice não clusterizado columnstore.

Í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 usando o ALTER INDEX instrução. 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 usando o incluir palavra-chave.

  • Não é possível incluir o ASC ou DESC palavras-chave para o índice de classificação. Os índices columnstore são ordenados de acordo com os algoritmos de compactação. A classificação eliminará muitos dos benefícios de desempenho.

  • Não é possível incluir colunas de objeto grande (LOB) do tipo nvarchar (max), varchar (max) e varbinary (max) em índices não clusterizados. Somente índices columnstore clusterizados oferecem suporte a tipos LOB, começando em SQL Server vNext versão. Observe que as versões anteriores não oferecem suporte a tipos de LOB em índices columnstore clusterizados e.

Cada coluna em um índice columnstore deve 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) (aplica-se a SQL Server vNext, no cluster apenas índices columnstore)

  • nchar [ ( n ) ]

  • varchar [ ( n ) ]

  • varchar (max) (aplica-se a SQL Server vNext, no cluster apenas índices columnstore)

  • char [ ( n ) ]

  • varbinary [( n )]

  • varbinary (max) (aplica-se a SQL Server vNext, no cluster apenas índices columnstore)

  • binary [ ( n ) ]

  • Identificador exclusivo (aplica-se a SQL Server 2014 e posterior)

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

  • ntext, texto e imagem

  • varchar (max), nvarchar (max) e varbinary (max) (aplica-se a SQL Server 2016 e versões anteriores e índices columnstore não clusterizados)

  • rowversion (e carimbo de data/hora)

  • sql_variant

  • Tipos CLR (hierarchyid e tipos espaciais)

  • xml

  • Identificador exclusivo (aplica-se a SQL Server 2012)

Índices ColumnStore não podem ser combinados com os seguintes recursos:

  • Colunas computadas

  • Compactação de página e de linha, e vardecimal formato de armazenamento (um índice columnstore já é compactado em um formato diferente.)

  • Replicação

  • Fluxo de arquivos

Você não pode usar cursores ou disparadores em uma tabela com um índice columnstore clusterizado. Essa restrição não se aplica a índices columnstore não clusterizado; Você pode usar cursores e disparadores em uma tabela com um índice não clusterizado columnstore.

Limitações do SQL Server 2014

Essas limitações se aplicam somente ao SQL Server 2014. Nesta versão, apresentamos os índices columnstore clusterizados atualizáveis. Índices não clusterizados columnstore eram ainda somente leitura.

  • Controle de alterações. Você não pode usar o controle de alterações com índices columnstore não clusterizados (NCCI) porque eles são somente leitura. Ele funciona para índices columnstore clusterizados (CCI).

  • O Change data capture. Você não pode usar a alteração de captura de dados de índice não clusterizado columnstore (NCCI) porque eles são somente leitura. Ele funciona para índices columnstore clusterizados (CCI).

  • Secundário legível. Você não pode acessar um índice clusterizado columnstore clusterizadas (CCI) de um secundário legível de um grupo de disponibilidade sempre OnReadable. Você pode acessar um índice não clusterizado columnstore (NCCI) de um secundário legível.

  • Multiple Active Result Sets (MARS). SQL Server 2014 usa MARS para conexões somente leitura para tabelas com um índice columnstore. No entanto, SQL Server 2014 não dá suporte a MARS para operações de DML (linguagem) de manipulação de dados simultâneas em uma tabela com um índice columnstore. Quando isso ocorre, o SQL Server encerrar as conexões e anular as transações.

Para obter informações sobre os benefícios de desempenho e limitações de índices columnstore, consulte visão geral de índices Columnstore.

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.

A. Converter um índice columnstore clusterizado

Este exemplo cria uma tabela como um heap e, depois, converte-o em um índice columnstore clusterizado chamado o cci_Simple. Isso altera o armazenamento da tabela inteira, de rowstore a columnstore.

CREATE TABLE 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 SimpleTable;  
GO  

B. Converta 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. Isso altera o armazenamento da tabela inteira, de rowstore a 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 CLUSTERED COLUMNSTORE INDEX cl_simple ON SimpleTable  
WITH (DROP_EXISTING = ON);  
GO  

C. Ao converter uma tabela rowstore em um índice columnstore, lidar com índices não clusterizados.

Este exemplo mostra como tratar os índices não clusterizados ao converter uma tabela rowstore em um índice columnstore. Na verdade, começando com SQL Server 2016 nenhuma ação especial é necessária; SQL Server definir automaticamente e recriar os índices não clusterizados no novo índice columnstore clusterizado.

Se você deseja 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. Não descarte os índices não clusterizados.

Em SQL Server 2012 e SQL Server 2014, você não pode criar um índice não clusterizado em um índice columnstore. Este exemplo mostra como nas versões anteriores é necessário descartar os índices não clusterizados antes de criar o índice columnstore.

  
--Create the table for use with this example.  
CREATE TABLE 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 SimpleTable (OrderDateKey);  
CREATE INDEX nc2_simple ON SimpleTable (DueDateKey);   
GO  
  
--SQL Server 2012 and SQL Server 2014: you need to drop the nonclustered indexes  
--in order to create the columnstore index.   
  
DROP INDEX SimpleTable.nc1_simple;  
DROP INDEX SimpleTable.nc2_simple;  
  
--Convert the rowstore table to a columnstore index.  
CREATE CLUSTERED COLUMNSTORE INDEX cci_simple ON 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.

Para converter uma tabela rowstore em uma tabela columnstore.

  1. Primeiro, crie uma pequena tabela a ser usada neste exemplo.

    --Create a rowstore table with a clustered index and a non-clustered index.  
    CREATE TABLE MyFactTable (  
        ProductKey [int] NOT NULL,  
        OrderDateKey [int] NOT NULL,  
         DueDateKey [int] NOT NULL,  
         ShipDateKey [int] NOT NULL )  
    )  
    WITH (  
        CLUSTERED INDEX ( ProductKey )  
    );  
    
    --Add a non-clustered index.  
    CREATE INDEX my_index ON MyFactTable ( ProductKey, OrderDateKey );  
    
    
  2. Remova todos os índices não clusterizados da tabela rowstore.

    --Drop all non-clustered indexes  
    DROP INDEX my_index ON MyFactTable;  
    
    
  3. Descarte o índice clusterizado.

    • Faça isso somente se desejar especificar um novo nome para o índice quando ele for convertido em um índice columnstore clusterizado. Se você não remover o índice clusterizado, o novo índice columnstore clusterizado terá o mesmo nome.

      System_CAPS_ICON_note.jpg Observação


      Pode ser mais fácil lembrar o nome do índice se você usar seu próprio nome. Todos os índices clusterizados do rowstore usam o nome padrão que é ' ClusteredIndex_<>>'.

    --Process for dropping a clustered index.  
    --First, look up the name of the clustered rowstore index.  
    --Clustered rowstore indexes always use the DEFAULT name ‘ClusteredIndex_<GUID>’.  
    SELECT i.name   
    FROM sys.indexes i   
    JOIN sys.tables t  
    ON ( i.type_desc = 'CLUSTERED' ) WHERE t.name = 'MyFactTable';  
    
    --Drop the clustered rowstore index.  
    DROP INDEX ClusteredIndex_d473567f7ea04d7aafcac5364c241e09 ON MyDimTable;  
    
    
  4. Converta a tabela rowstore em uma tabela columnstore com um índice columnstore clusterizado.

    --Option 1: Convert to columnstore and name the new clustered columnstore index MyCCI.  
    CREATE CLUSTERED COLUMNSTORE INDEX MyCCI ON MyFactTable;  
    
    --Option 2: Convert to columnstore and use the rowstore clustered   
    --index name for the columnstore clustered index name.  
    --First, look up the name of the clustered rowstore index.  
    SELECT i.name   
    FROM sys.indexes i  
    JOIN sys.tables t   
    ON ( i.type_desc = 'CLUSTERED' )  
    WHERE t.name = 'MyFactTable';  
    
    --Second, create the clustered columnstore index and   
    --Replace ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
    --with the name of your clustered index.  
    CREATE CLUSTERED COLUMNSTORE INDEX   
    ClusteredIndex_d473567f7ea04d7aafcac5364c241e09  
     ON 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 ci_MyTable   
ON MyFactTable  
WITH ( DROP EXISTING = ON );  

F. Converter uma tabela columnstore em um heap rowstore

Para converter uma tabela columnstore em um heap rowstore, basta remover o índice columnstore clusterizado.

DROP INDEX MyCCI   
ON MyFactTable;  

G. Desfragmentar recriando o índice columnstore clusterizado inteiro

Aplica-se a: SQL Server 2014

Há duas maneiras de recriar o índice columnstore clusterizado completo. Você pode usar CREATE CLUSTERED COLUMNSTORE INDEX, ou ALTER INDEX (Transact-SQL) e a opção de RECONSTRUÇÃO. Ambos os métodos geram os mesmos resultados.

System_CAPS_ICON_note.jpg Observação


Começando com o SQL Server 2016, use ALTER INDEX REORGANIZE em vez de reconstruir com os métodos descritos neste exemplo.

--Determine the Clustered Columnstore Index name of MyDimTable.  
SELECT i.object_id, i.name, t.object_id, t.name   
FROM sys.indexes i   
JOIN sys.tables t  
ON (i.type_desc = 'CLUSTERED COLUMNSTORE')  
WHERE t.name = 'RowstoreDimTable';  
  
--Rebuild the entire index by using CREATE CLUSTERED INDEX.  
CREATE CLUSTERED COLUMNSTORE INDEX my_CCI   
ON MyFactTable  
WITH ( DROP_EXISTING = ON );  
  
--Rebuild the entire index by using ALTER INDEX and the REBUILD option.  
ALTER INDEX my_CCI  
ON MyFactTable  
REBUILD PARTITION = ALL  
WITH ( DROP_EXISTING = ON );  
  

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

Este exemplo cria um índice não clusterizado columnstore em uma tabela rowstore. Somente um índice columnstore pode ser criado nesta situação. O índice columnstore exige armazenamento extra, pois ela contém uma cópia dos dados na tabela rowstore. Este exemplo cria uma tabela simples e um índice clusterizado e, em seguida, demonstra a sintaxe de criação de um índice não clusterizado 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. Criar um índice columnstore não clusterizado simples usando 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 usando tabelas particionadas, consulte visão geral de índices Columnstore.

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

O exemplo a seguir cria um índice columnstore não clusterizado filtrados na tabela billofmaterials o AdventureWorks2012 banco de dados. O predicado de filtro pode incluir colunas que não sejam de chave no índice filtrado. O predicado deste 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 não clusterizado columnstore

Aplica-se a: do SQL Server 2012 ao SQL Server 2014.

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.

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

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

  • Altere um índice clusterizado em um índice columnstore clusterizado.

  • Recompile um índice columnstore clusterizado.

Este exemplo cria a tabela de xDimProduct como uma tabela rowstore com um índice clusterizado e, em seguida, usa criar índice de COLUMNSTORE CLUSTERIZADO para alterar a tabela de uma tabela rowstore em 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;  
  
--Change the existing clustered index   
--to a clustered columnstore index with the same name.  
--Look up the name of the index before running this statement.  
CREATE CLUSTERED COLUMNSTORE INDEX <index_name>   
ON xdimProduct   
WITH ( DROP_EXISTING = ON );  

F. Recriar um índice columnstore clusterizado

Criar o 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 );  

G. 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 apenas fazendo essa operação com uma pequena tabela ou uma tabela vazia. Levará algum tempo para descartar um índice columnstore clusterizado grandes e recompilar com um nome diferente.

Usando o índice de 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 );  

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

Pode haver uma situação para a qual você deseja remover um índice columnstore clusterizado e crie um índice clusterizado. Isso armazena a tabela no formato rowstore. Este exemplo converte uma tabela columnstore em uma tabela rowstore com um índice clusterizado com o mesmo nome. Nenhum dos dados são perdido. Todos os dados passam para a tabela rowstore, e as colunas listadas tornam-se as colunas de chave no índice clusterizado.

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

I. Converter uma tabela columnstore para um heap rowstore

Use DROP INDEX (SQL Server PDW) para descartar o índice columnstore clusterizado e converter a tabela em um heap rowstore. Este exemplo converte a tabela de cci_xDimProduct em um heap rowstore. A tabela continuará a ser distribuído, mas é armazenado 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;  

Contribuições da comunidade

ADICIONAR
Mostrar: