-- Example 27.
1
USE sample;
SELECT * INTO FactInternetSales
FROM AdventureWorksDW.dbo.FactInternetSales;
GO
CREATE CLUSTERED COLUMNSTORE INDEX
cl_factinternetsales ON FactInternetSales
WITH ( DATA_COMPRESSION = COLUMNSTORE);
-- Example 27.2
USE sample;
GO
DROP INDEX
cl_factinternetsales ON factinternetsales;
GO
CREATE NONCLUSTERED COLUMNSTORE INDEX cs_index1
ON FactInternetSales (OrderDateKey, ShipDateKey, UnitPrice);
-- Example 27.3
USE sample;
CREATE NONCLUSTERED COLUMNSTORE INDEX i1 ON
FactInternetSales (UnitPrice)
WHERE OrderDateKey IS NULL;
-- Example 27.4
SELECT i.name, p.object_id, p.index_id, i.type_desc,
COUNT(*) AS number_of_segments
FROM sys.column_store_segments AS s
INNER JOIN sys.partitions AS p
ON s.hobt_id = p.hobt_id
INNER JOIN sys.indexes AS i
ON p.object_id = i.object_id
WHERE i.type = 6
GROUP BY i.name, p.object_id, p.index_id, i.type_desc ;
-- Example 27.5
USE sample;
SELECT i.object_id, o.name,
i.name AS IndexName, i.index_id, i.type_desc
FROM sys.indexes AS i
JOIN sys.column_store_row_groups AS row_group
ON i.object_id = row_group.object_id
JOIN sys.objects o ON i.object_id = o.object_id
AND i.index_id = row_group.index_id;
-- Example 27.6
USE AdventureworksDW2016_EXT
GO
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT s.SalesTerritoryRegion, SUM(f.SalesAmount) 'Total_Sales',
COUNT(distinct f.Resellerkey) as 'Resellers'
FROM FactResellerSalesXL_PageCompressed f
INNER JOIN DimDate d ON f.OrderDateKey= d.Datekey
INNER JOIN DimSalesTerritory s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN DimEmployee e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2015' and '1/1/2017'
GROUP BY s.SalesTerritoryRegion
ORDER BY Total_Sales
SET STATISTICS IO OFF
SET STATISTICS TIME OFF;
-- Example 27.7
USE AdventureworksDW2016_EXT
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
SET STATISTICS TIME ON
SELECT s.SalesTerritoryRegion, SUM(f.SalesAmount) 'Total_Sales',
COUNT(distinct f.Resellerkey) as 'Resellers'
FROM FactResellerSalesXL_CCI f
INNER JOIN dbo.DimDate d ON f.OrderDateKey= d.Datekey
INNER JOIN dbo.DimSalesTerritory s on s.SalesTerritoryKey=f.SalesTerritoryKey
INNER JOIN dbo.DimEmployee e on e.EmployeeKey=f.EmployeeKey
WHERE FullDateAlternateKey between '1/1/2015' and '1/1/2017'
GROUP BY s.SalesTerritoryRegion
ORDER BY Total_Sales
SET STATISTICS IO OFF
SET STATISTICS TIME OFF;
-- Example 27.8
USE sample;
SELECT * INTO FactInternetSales
FROM AdventureWorksDW.dbo.FactInternetSales;
GO
INSERT INTO FactInternetSales
SELECT * FROM AdventureWorksDW.dbo.FactInternetSales;
GO 6
-- Example 27.9
USE sample;
SELECT * INTO DimCustomer
FROM AdventureWorksDW.dbo.DimCustomer;
GO
SELECT * INTO DimDate
FROM AdventureWorksDW.dbo.DimDate;
-- Example 27.10
USE sample;
CREATE NONCLUSTERED COLUMNSTORE INDEX CLI_CS_IFactInternetSales
ON dbo.FactInternetSales(OrderDateKey, CustomerKey, SalesAmount);
-- Example 27.11
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
EXEC sp_configure 'max degree of parallelism', 4;
GO
RECONFIGURE WITH OVERRIDE;
GO
-- Example 27.12
USE sample;
SELECT c.CommuteDistance, d.CalendarYear,
SUM(f.SalesAmount) TotalSales
FROM dbo.FactInternetSales as f
INNER JOIN dbo.DimCustomer as c ON
f.CustomerKey = c.CustomerKey
INNER JOIN dbo.DimDate d ON
d.DateKey = f.OrderDateKey
GROUP BY c.CommuteDistance, d.CalendarYear;
