[edit]
1 蟲覯 #
sample db ![[http]](/moniwiki/imgs/http.png)
蠍 <--- れ企 .
蠍 <--- れ企 .
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 INSERTSELECT 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
覃讌 35330, 譴 15, 1, 譴 1
レ 碁煙り 企 一危磯ゼ 一危誤 朱襦 INSERT 覓語 ろ給. INSERT 覓語 ろ蠍 レ 碁煙るゼ 觜燕螻 INSERT(襯) 襭 レ 碁煙るゼ れ 燕 覲伎.
螳 伎 . レ 碁煙り 企 一危磯ゼ 一危誤 朱襦 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)
鏤