Columnstore Index – 2ª Parte: Melhorias no SQL Server 2016

Na 1ª Parte do post sobre Columnstore Index mostrei as principais características deste índice e suas limitações no SQL Server 2014, para acessar utilize o link abaixo:

http://sqlserver-brasil.blogspot.com.br/2016/09/columnstore-index-1-parte.html

Nesta 2ª Parte vou mostrar algumas melhorias feitas no Columnstore Index pela Microsoft no SQL Server 2016, veja a lista abaixo:

- Agora uma tabela pode ter Nonclustered Columnstore Index e ser atualizada, nas versões anteriores este índice era somente leitura.

- Nonclustered Columnstore Index pode conter cláusula de filtro.

- Tabelas in-memory podem ter Columnstore Index.

- Tabelas com Clustered Columnstore Index podem ter chave primária e chave estrangeira.

ATUALIZAÇÃO

Utilizando o mesmo Script da 1ª Parte do post no SQL Server 2016, observamos que a limitação de atualização foi retirada.

CREATE TABLE dbo.MySalesOrderDetail(

SalesOrderID int NOT NULL,

SalesOrderDetailID int NOT NULL,

CarrierTrackingNumber nvarchar(25) NULL,

OrderQty smallint NOT NULL,

ProductID int NOT NULL,

SpecialOfferID int NOT NULL,

UnitPrice money NOT NULL,

UnitPriceDiscount money NOT NULL,

LineTotal numeric(38, 6) NOT NULL,

rowguid uniqueidentifier NOT NULL,

ModifiedDate datetime NOT NULL)

go

CREATE INDEX NCL_MySalesOrderDetail ON dbo.MySalesOrderDetail (ProductID)

INCLUDE (UnitPrice, OrderQty)

go

CREATE NONCLUSTERED COLUMNSTORE INDEX IX_MySalesOrderDetail_ColumnStore_ProductID

ON MySalesOrderDetail (ProductID,UnitPrice, OrderQty)

go

INSERT INTO dbo.MySalesOrderDetail

SELECT S1.* FROM AdventureWorks.Sales.SalesOrderDetail S1

No SQL Server 2016 a atualização ocorre sem problema, já no SQL Server 2014 falha com a mensagem de erro abaixo:

Msg 35330, Level 15, State 1, Line 25

INSERT statement failed because data cannot be updated in a table that has a nonclustered columnstore index. Consider disabling the columnstore index before issuing the INSERT statement, and then rebuilding the columnstore index after INSERT has completed.

COLUMNSTORE INDEX COM FILTRO

Outra novidade no SQL Server 2016 é a possibilidade de definir um Indice Columnstore com filtro, do mesmo modo que fazemos nos índices Nonclustered desde o SQL Server 2008.

Imagine um cenário onde temos uma tabela de vendas com um grande histórico, o Script abaixo cria e inclui na tabela SalesOrderHearder registros de vendas de 1986 até 2007:

CREATE TABLE dbo.SalesOrderHeader(

SalesOrderID int NOT NULL,

OrderDate datetime NOT NULL,

Status tinyint NOT NULL,

OnlineOrderFlag bit NOT NULL,

SalesOrderNumber nvarchar(25) NOT NULL,

CustomerID int NOT NULL,

SalesPersonID int NULL,

TerritoryID int NULL,

SubTotal money NOT NULL,

TaxAmt money NOT NULL,

Freight money NOT NULL,

TotalDue money NOT NULL,

Comment nvarchar(128) NULL)

go

declare @i int = 1

while @i < 20 begin

       insert dbo.SalesOrderHeader

       select SalesOrderID, DATEADD(YY,@i * -1,OrderDate), Status, OnlineOrderFlag,

       SalesOrderNumber, CustomerID, SalesPersonID, TerritoryID, 

       SubTotal, TaxAmt, Freight, TotalDue, Comment

       from AdventureWorks.Sales.SalesOrderHeader

       set @i += 1

end

go

select max(OrderDate),min(OrderDate)

from dbo.SalesOrderHeader

-- Datas de 1986 a 2007

As consultas de dados históricos tendem a ser bem diferentes dos dados atuais, no histórico muitas linhas e várias operações agregadas (GROUP BY), já nos dados recentes poucos registros e consultas mais simples.  A ideia seria criar um índice Columnstore para o histórico, mais eficiente para este cenário, e índice Nonclustered para os dados recentes. Considerei “dados recentes” os dois últimos anos 2006 e 2007, já o histórico até 2005.

-- Indice Columnstore

CREATE NONCLUSTERED COLUMNSTORE INDEX IXColumn_SalesOrderHeader_OrderDate

ON dbo.SalesOrderHeader (OrderDate,SalesOrderID,SalesOrderNumber,CustomerID,SubTotal)

WHERE OrderDate < '20060101'

-- Indice Nonclustered

CREATE INDEX IX_SalesOrderHeader_OrderDate

ON dbo.SalesOrderHeader (OrderDate)

INCLUDE (SalesOrderID,SalesOrderNumber,CustomerID,SubTotal)

WHERE OrderDate >= '20060101'

Consultas com datas a partir de 2006 irão utilizar o índice Nonclustered, com datas anteriores a 2006 Columnstore.

-- Utiliza o indice Nonclustered

SELECT CustomerID,sum(SubTotal) CustomerTotal, count(*) OrderQt

FROM dbo.SalesOrderHeader

WHERE OrderDate >= '20060401' AND OrderDate < '20060501'

GROUP BY CustomerID

-- Utiliza o indice Columnstore

SELECT CustomerID,sum(SubTotal) CustomerTotal, count(*) OrderQt

FROM dbo.SalesOrderHeader

WHERE OrderDate >= '20020401' AND OrderDate < '20020501'

GROUP BY CustomerID

O que acontece se o intervalo de datas conter registros de ambos os índices? Vamos analisar o plano de execução abaixo:

-- Utiliza ambos os indices

SELECT CustomerID,sum(SubTotal) CustomerTotal, count(*) OrderQt

FROM dbo.SalesOrderHeader

WHERE OrderDate >= '20050101' AND OrderDate < '20070101'

GROUP BY CustomerID

O Otimizador de Consultas do SQL Server 2016 utiliza ambos os índices e posteriormente concatena o resultado!

CONCLUSÃO

O SQL Server 2016 retirou as principais limitações do Columnstore Index, aumentando os cenários de uso desta funcionalidade.

Até o próximo post.

Saudações Tricolores,

Landry