#title Columnstore Indexes
[[TableOfContents]]

==== 구버전 ====
sample db는 [http://msftdbprodsamples.codeplex.com/releases/view/55330 여기] <--- 에서 다운로드 한다. 
{{{
USE AdventureWorksDWDenali;
GO

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT 
FOR VALUES (
    20050701, 20050801, 20050901, 20051001, 20051101, 20051201, 
    20060101, 20060201, 20060301, 20060401, 20060501, 20060601, 
    20060701, 20060801, 20060901, 20061001, 20061101, 20061201, 
    20070101, 20070201, 20070301, 20070401, 20070501, 20070601, 
    20070701, 20070801, 20070901, 20071001, 20071101, 20071201, 
    20080101, 20080201, 20080301, 20080401, 20080501, 20080601, 
    20080701, 20080801, 20080901, 20081001, 20081101, 20081201
) 
GO

CREATE PARTITION SCHEME [ByOrderDateMonthRange] 
AS PARTITION [ByOrderDateMonthPF] 
ALL TO ([PRIMARY]) 
GO

-- Create a partitioned version of the FactResellerSales table
CREATE TABLE [dbo].[FactResellerSalesPtnd]( 
    [ProductKey] [int] NOT NULL, 
    [OrderDateKey] [int] NOT NULL, 
    [DueDateKey] [int] NOT NULL, 
    [ShipDateKey] [int] NOT NULL, 
    [ResellerKey] [int] NOT NULL, 
    [EmployeeKey] [int] NOT NULL, 
    [PromotionKey] [int] NOT NULL, 
    [CurrencyKey] [int] NOT NULL, 
    [SalesTerritoryKey] [int] NOT NULL, 
    [SalesOrderNumber] [nvarchar](20) NOT NULL, 
    [SalesOrderLineNumber] [tinyint] NOT NULL, 
    [RevisionNumber] [tinyint] NULL, 
    [OrderQuantity] [smallint] NULL, 
    [UnitPrice] [money] NULL, 
    [ExtendedAmount] [money] NULL, 
    [UnitPriceDiscountPct] [float] NULL, 
    [DiscountAmount] [float] NULL, 
    [ProductStandardCost] [money] NULL, 
    [TotalProductCost] [money] NULL, 
    [SalesAmount] [money] NULL, 
    [TaxAmt] [money] NULL, 
    [Freight] [money] NULL, 
    [CarrierTrackingNumber] [nvarchar](25) NULL, 
    [CustomerPONumber] [nvarchar](25) NULL
) ON ByOrderDateMonthRange(OrderDateKey);
GO

-- Using simple or bulk logged recovery mode, and then the TABLOCK 
-- hint on the target table of the INSERT…SELECT is a best practice
-- because it causes minimal logging and is therefore much faster.
ALTER DATABASE AdventureWorksDWDenali SET RECOVERY SIMPLE;
GO

-- Copy the data from the FactResellerSales into the new table
-- 온라인 도움말에는 여기가 잘못되어 있다. 아래와 같이 해주어야 한다. 
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT 
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, 
	PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, 
	RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
	DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, 
	Freight, CarrierTrackingNumber, CustomerPONumber
FROM dbo.FactResellerSales;
GO
}}}

coloumn store index를 사용했을 경우
{{{
-- Create the columnstore index
CREATE NONCLUSTERED COLUMNSTORE INDEX [csindx_FactResellerSalesPtnd]
ON [FactResellerSalesPtnd]
( 
    [ProductKey], 
    [OrderDateKey], 
    [DueDateKey], 
    [ShipDateKey], 
    [CustomerKey], 
    [EmployeeKey], 
    [PromotionKey], 
    [CurrencyKey], 
    [SalesTerritoryKey], 
    [SalesOrderNumber], 
    [SalesOrderLineNumber], 
    [RevisionNumber], 
    [OrderQuantity], 
    [UnitPrice], 
    [ExtendedAmount], 
    [UnitPriceDiscountPct], 
    [DiscountAmount], 
    [ProductStandardCost], 
    [TotalProductCost], 
    [SalesAmount], 
    [TaxAmt], 
    [Freight], 
    [CarrierTrackingNumber], 
    [CustomerPONumber], 
    [OrderDate],
    [DueDate],
    [ShipDate]
);

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM FactResellerSalesPtnd
GROUP BY SalesTerritoryKey;
--테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
--테이블 'FactResellerSalesPtnd'. 검색 수 1, 논리적 읽기 수 49, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.

/*
SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory
FROM dbo.FactResellerSales
GROUP BY SalesTerritoryKey;

--테이블 'Worktable'. 검색 수 0, 논리적 읽기 수 0, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
--테이블 'FactResellerSales'. 검색 수 1, 논리적 읽기 수 2982, 물리적 읽기 수 0, 미리 읽기 수 0, LOB 논리적 읽기 수 0, LOB 물리적 읽기 수 0, LOB 미리 읽기 수 0.
*/
}}}
attachment:ColumnstoreIndexes/cs01.png


다시 데이터를 입력해보면.. 아래와 같은 메시지가 나온다.
{{{
INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT 
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, 
	PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, 
	RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
	DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, 
	Freight, CarrierTrackingNumber, CustomerPONumber
FROM dbo.FactResellerSales;
GO
}}}


 ''메시지 35330, 수준 15, 상태 1, 줄 1''
 ''열 저장소 인덱스가 있는 테이블에서 데이터를 업데이트할 수 없으므로 INSERT 문이 실패했습니다. INSERT 문을 실행하기 전에 열 저장소 인덱스를 비활성화하고 INSERT을(를) 완료한 후 열 저장소 인덱스를 다시 작성해 보십시오.''

아래와 같이 해야 한다. 
{{{
alter index csindx_FactResellerSalesPtnd 
on dbo.FactResellerSalesPtnd disable;

INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)
SELECT 
	ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, 
	PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, 
	RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, 
	DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, 
	Freight, CarrierTrackingNumber, CustomerPONumber
FROM dbo.FactResellerSales;
GO

alter index csindx_FactResellerSalesPtnd 
on dbo.FactResellerSalesPtnd rebuild;
}}}


coloumn store index 사이즈 계산
{{{
SELECT SUM(on_disk_size_MB) AS TotalSizeInMB
FROM
(
   (SELECT SUM(css.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_segments AS css
        ON css.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
  UNION ALL
   (SELECT SUM(csd.on_disk_size)/(1024.0*1024.0) on_disk_size_MB
    FROM sys.indexes AS i
    JOIN sys.partitions AS p
        ON i.object_id = p.object_id 
    JOIN sys.column_store_dictionaries AS csd
        ON csd.hobt_id = p.hobt_id
    WHERE i.object_id = object_id('FactResellerSalesPtnd') 
    AND i.type_desc = 'NONCLUSTERED COLUMNSTORE') 
) AS SegmentsPlusDictionary
}}}

colum store index에 대한 정보는 다음의 view들을 참고하면 된다. 

 * sys.column_store_index_stats (Transact-SQL)
 * sys.column_store_dictionaries (Transact-SQL)
 * sys.column_store_segments (Transact-SQL)
 
==== 2014에서는.. ====
Clustered Column Store
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/05/04/clustered-column-store-factors-that-impact-size-of-a-rowgroup.aspx Factors that impact size of a RowGroup]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-and-isolation-level.aspx Concurrency and Isolation Level]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-with-insert-operations.aspx Concurrency with INSERT Operations]
 * [http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/28/clustered-column-store-index-bulk-loading-the-data.aspx Bulk Loading the Data]

==== 2016 ====
 * [http://blogs.technet.com/b/dataplatforminsider/archive/2015/12/09/real-time-operational-analytics-using-in-memory-technology.aspx Real-Time Operational Analytics Using In-Memory Technology]
 * [http://blogs.technet.com/b/dataplatforminsider/archive/2015/12/08/speeding-up-business-analytics-using-in-memory-technology-in-sql-server-2016-and-azure-sql-database.aspx Speeding up Business Analytics Using In-Memory Technology]