-- You can get the AdventureWorksDW2014 database from http://msftdbprodsamples.codeplex.com/releases/view/125550 USE [master]; GO ALTER DATABASE [AdventureWorksDW2014] MODIFY FILE ( NAME = N'AdventureWorksDW2014_Data', SIZE = 10240000KB , FILEGROWTH = 1024000KB ); GO ALTER DATABASE [AdventureWorksDW2014] MODIFY FILE ( NAME = N'AdventureWorksDW2014_Log', SIZE = 2048000KB , FILEGROWTH = 1024000KB ); GO -- Current row counts USE [AdventureWorksDW2014]; GO SELECT COUNT(*) FROM [dbo].[FactInternetSales]; GO --60398 SELECT COUNT(*) FROM [dbo].[FactProductInventory]; GO --776286 ALTER TABLE [dbo].[FactInternetSalesReason] DROP CONSTRAINT [FK_FactInternetSalesReason_FactInternetSales] GO ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [PK_FactProductInventory]; GO ALTER TABLE [dbo].[FactProductInventory] DROP CONSTRAINT [FK_FactProductInventory_DimDate] GO --------------- --http://www.sqlskills.com/blogs/joe/row-and-batch-execution-modes-and-columnstore-indexes/ USE [AdventureWorksDW2014]; GO SET NOCOUNT ON; INSERT dbo.FactInternetSales ( ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate ) SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)), 20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate FROM dbo.FactInternetSales; GO 3 SELECT COUNT(*) FROM [dbo].FactInternetSales; GO --483184 --http://www.sqlskills.com/blogs/joe/row-and-batch-execution-modes-and-columnstore-indexes/ USE [AdventureWorksDW2014]; GO CREATE NONCLUSTERED COLUMNSTORE INDEX [NCI_FactInternetSales] ON [dbo].[FactInternetSales] ( [OrderDateKey], [CustomerKey], [SalesAmount] ) WITH (DROP_EXISTING = OFF) ON [PRIMARY]; GO --------------- --http://www.sqlskills.com/blogs/joe/row-and-batch-execution-modes-and-columnstore-indexes/ USE [AdventureWorksDW2014]; GO SET NOCOUNT ON; INSERT dbo.FactInternetSales ( ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, SalesOrderNumber, SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate ) SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey, CustomerKey, PromotionKey, CurrencyKey, SalesTerritoryKey, LEFT(CAST(NEWID() AS NVARCHAR(36)), 20), SalesOrderLineNumber, RevisionNumber, OrderQuantity, UnitPrice, ExtendedAmount, UnitPriceDiscountPct, DiscountAmount, ProductStandardCost, TotalProductCost, SalesAmount, TaxAmt, Freight, CarrierTrackingNumber, CustomerPONumber, OrderDate, DueDate, ShipDate FROM dbo.FactInternetSales; GO 2 SELECT COUNT(*) FROM [dbo].FactInternetSales; GO --1932736 ------------ --Delete and update some rows on table FactInternetSales: SET NOCOUNT OFF; USE [AdventureWorksDW2014]; GO DELETE [dbo].FactInternetSales WHERE OrderDateKey < '20120101'; --(71360 row(s) affected) UPDATE [dbo].[FactInternetSales] SET OrderQuantity = 0 WHERE OrderDateKey < '20130101'; --(108704 row(s) affected) --------------- USE [AdventureWorksDW2014]; GO INSERT INTO [dbo].[FactProductInventory] ( [ProductKey], [DateKey], [MovementDate], [UnitCost], [UnitsIn], [UnitsOut], [UnitsBalance] ) SELECT [ProductKey], [DateKey] - (10000 * CAST(ABS(CHECKSUM(NewId())) % 20 AS INT)), CAST( DATEADD(YEAR, -CAST(ABS(CHECKSUM(NewId())) % 20 AS INT), [MovementDate]) AS DATE), [UnitCost], [UnitsIn] + CAST(ABS(CHECKSUM(NewId())) % 2000 AS INT), [UnitsOut] + CAST(ABS(CHECKSUM(NewId())) % 2000 AS INT), [UnitsBalance] + CAST(ABS(CHECKSUM(NewId())) % 4000 AS INT) FROM [dbo].[FactProductInventory] ORDER BY [ProductKey] ASC, [DateKey] ASC; GO 3 SELECT COUNT(*) FROM [dbo].[FactProductInventory]; GO --6210288 ------------ USE [AdventureWorksDW2014]; GO -- Creating a clustered index with PAGE data compression CREATE CLUSTERED INDEX [CCI_FactProductInventory] ON [dbo].[FactProductInventory] ( [ProductKey] ASC, [DateKey] ASC )WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]; GO --Create it again with MAXDOP 1 to get better sorting of the data: --http://www.nikoport.com/2014/04/16/clustered-columnstore-indexes-part-29-data-loading-for-better-segment-elimination/ CREATE CLUSTERED COLUMNSTORE INDEX [CCI_FactProductInventory] ON [dbo].[FactProductInventory] WITH (DROP_EXISTING = ON, DATA_COMPRESSION = COLUMNSTORE, MAXDOP = 1); GO ------------- USE [AdventureWorksDW2014]; GO INSERT INTO [dbo].[FactProductInventory] ( [ProductKey], [DateKey], [MovementDate], [UnitCost], [UnitsIn], [UnitsOut], [UnitsBalance] ) SELECT [ProductKey], [DateKey] - (10000 * CAST(ABS(CHECKSUM(NewId())) % 20 AS INT)), CAST( DATEADD(YEAR, -CAST(ABS(CHECKSUM(NewId())) % 20 AS INT), [MovementDate]) AS DATE), [UnitCost], [UnitsIn] + CAST(ABS(CHECKSUM(NewId())) % 2000 AS INT), [UnitsOut] + CAST(ABS(CHECKSUM(NewId())) % 2000 AS INT), [UnitsBalance] + CAST(ABS(CHECKSUM(NewId())) % 4000 AS INT) FROM [dbo].[FactProductInventory] ORDER BY [ProductKey] ASC, [DateKey] ASC; GO SELECT COUNT(*) FROM [dbo].[FactProductInventory]; GO --12420576 ------------ --Delete and update some rows on table [FactProductInventory]: SET NOCOUNT OFF; USE [AdventureWorksDW2014]; GO DELETE [dbo].[FactProductInventory] WHERE [DateKey] < '19600101'; --(94350 row(s) affected) UPDATE [dbo].[FactProductInventory] SET UnitsBalance = 0 WHERE [DateKey] < '19700101'; --(427258 row(s) affected) ------------------------------------------------------------------------------------------- USE [AdventureWorksDW2014]; GO CREATE NONCLUSTERED INDEX [NI_FactProductInventory] ON [dbo].[FactProductInventory] ([UnitsBalance]); GO