Como atualizar uma tabela com um índice columstore

Neste artigo, mostrei como criar um índice columnstore em uma tabela e como algumas
queries são capazes de reduzir significamente o IO necessário, e assim aumentar
a performance ao alavancar esse novo recurso. Mas uma vez que o índice columnstore é adicionado à tabela, ela se torna somente de leitura, pois não pode ser
atualizada. Tentar inserir uma nova linha na tabela irá resultar em erro:

insert into sales ([date],itemid, price, quantity) values ('20110713', 1,1.0,1);

Msg 35330, Level
15, State 1, Line 1


INSERT statement failed because data cannot be updated in a table with a
columnstore index. Consider disabling the columnstore index before issuing the
INSERT statement, then rebuilding the columnstore index after INSERT is
complete.

Essa mensagem de erro
recomenda uma solução alternativa, mas reconstruir o índice columnstore para
atualizações pode ser proibitivamente dispendioso. Para os cenários de DW e BI
nos quais os índices columnstore estão sendo o alvo, existe uma solução muito
melhor: usar o particionamento de tabelas.

Com o SQL Server 11, o limite de mil partilhas por tabela aumentou para 15 mil partilhas e, com esse novo limite,
você pode configurar os processos ETL para serem atualizados todos os dias para
uma nova partição e ainda reter muitos anos de dados. Os processos ETL podem
carregar os dados diários para dentro de uma staging table, criar um índice columnstore no staging table, e então usar a operação rápida alter table… switch
para “trocar” os novos dados. Usando o mesmo exemplo do meu artigo anterior,
vamos criar uma staging table com a estrutura idêntica como a da tabela de
vendas:

create table sales_staging (
	[id] int not null identity (1000000,1),
	[date] date not null,
	itemid smallint not null,
	price money not null,
	quantity numeric(18,4) not null,
	constraint check_date check ([date] = '20110716')) on [PRIMARY];
go

create unique clustered index cdx_sales_staging_date_id
   on sales_staging ([date], [id]) on [PRIMARY];
go

Note como a staging table tem uma verificação de restrição que força os
dados a serem válidos para a próxima partição em que serão trocadas. Agora
vamos popular a staging table com alguns fatos de vendas bobos:

set nocount on
go

declare @i int = 0;
begin transaction;
while @i < 250000
begin
	insert into sales_staging ([date], itemid, price, quantity)
		values ('20110716', rand()*10000, rand()*100 + 100, rand()* 10.000+1);
	set @i += 1;
	if @i % 10000 = 0
	begin
		raiserror (N'Inserted %d', 0, 1, @i);
		commit;
		begin tran;
	end
end
commit;
go

Agora que nossos processos falsos de ETL terminaram de preparar os dados
dos últimos dias de vendas em uma staging table, vamos adicionar um índice columnstore idêntico com aquela na tabela de vendas real:

create columnstore index cs_sales_price_staging
          on sales_staging ([date], itemid, price, quantity);
go

OK, nossa staging table está completa, então vamos trocar para a
“grande” tabela de vendas:

alter partition scheme ps next used [PRIMARY];
alter partition function pf() split range ('20110717');
go

alter table sales_staging switch to sales partition $PARTITION.PF('20110716');
go

É isso aí! Apenas atualizamos
nossa tabela de vendas com as informações do último dia, apesar do fato de que ela
continha um índice columnstore, sem desabilitá-lo. A
conta de partições aumentada suportada pelo SQL Server combinada com o fato de
que índices columnstore alinhados são suportados pela partição rápida para
operações de troca fazem com que o uso de tabelas com índices columnstore sejam atualizáveis na prática, se o processo ETL utilizar uma staging table e o
ETL corresponder ao esquema de particionamento. 

?

Texto original disponível em http://rusanu.com/2011/07/13/how-to-update-a-table-with-a-columnstore-index/