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