1 구버전

2 2014에서는..

3 2016

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.
*/

다시 데이터를 입력해보면.. 아래와 같은 메시지가 나온다.

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

Clustered Column Store

• Factors that impact size of a RowGroup(http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/05/04/clustered-column-store-factors-that-impact-size-of-a-rowgroup.aspx)
  
• Concurrency and Isolation Level(http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-and-isolation-level.aspx)
  
• Concurrency with INSERT Operations(http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/27/clustered-column-store-index-concurrency-with-insert-operations.aspx)
  
• Bulk Loading the Data(http://blogs.msdn.com/b/sqlserverstorageengine/archive/2014/07/28/clustered-column-store-index-bulk-loading-the-data.aspx)
  
  

• Real-Time Operational Analytics Using In-Memory Technology(http://blogs.technet.com/b/dataplatforminsider/archive/2015/12/09/real-time-operational-analytics-using-in-memory-technology.aspx)
  
• Speeding up Business 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)