Contents

1 구버전

2 2014에서는..

3 2016

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

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

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

• sys.column_store_index_stats (Transact-SQL)
  
• sys.column_store_dictionaries (Transact-SQL)
  
• sys.column_store_segments (Transact-SQL)
  
  

• Factors that impact size of a RowGroup
  
• Concurrency and Isolation Level
  
• Concurrency with INSERT Operations
  
• Bulk Loading the Data
  
  

• Real-Time Operational Analytics Using In-Memory Technology
  
• Speeding up Business Analytics Using In-Memory Technology