Scribd
Upload
2 views4 pages

Columnstore Indexing in SQL Queries

The document contains SQL examples demonstrating various operations on a database, including creating and dropping indexes, selecting data into new tables, and performing aggregate queries. It showcases the use of clustered and nonclustered columnstore indexes, as well as data manipulation techniques involving joins and groupings. The examples are executed within the context of a sample database and involve sales data analysis from the AdventureWorksDW database.

Uploaded by

riyasathsafran
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
2 views4 pages

Columnstore Indexing in SQL Queries

The document contains SQL examples demonstrating various operations on a database, including creating and dropping indexes, selecting data into new tables, and performing aggregate queries. It showcases the use of clustered and nonclustered columnstore indexes, as well as data manipulation techniques involving joins and groupings. The examples are executed within the context of a sample database and involve sales data analysis from the AdventureWorksDW database.

Uploaded by

riyasathsafran
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

-- Example 27.

1
USE sample;
SELECT * INTO FactInternetSales
FROM [Link];
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 [Link], p.object_id, p.index_id, i.type_desc,
COUNT(*) AS number_of_segments
FROM sys.column_store_segments AS s
INNER JOIN [Link] AS p
ON s.hobt_id = p.hobt_id
INNER JOIN [Link] AS i
ON p.object_id = i.object_id
WHERE [Link] = 6
GROUP BY [Link], p.object_id, p.index_id, i.type_desc ;
-- Example 27.5
USE sample;
SELECT i.object_id, [Link],
[Link] AS IndexName, i.index_id, i.type_desc
FROM [Link] AS i
JOIN sys.column_store_row_groups AS row_group
ON i.object_id = row_group.object_id
JOIN [Link] 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 [Link], SUM([Link]) 'Total_Sales',
COUNT(distinct [Link]) as 'Resellers'
FROM FactResellerSalesXL_PageCompressed f
INNER JOIN DimDate d ON [Link]= [Link]
INNER JOIN DimSalesTerritory s on [Link]=[Link]
INNER JOIN DimEmployee e on [Link]=[Link]
WHERE FullDateAlternateKey between '1/1/2015' and '1/1/2017'
GROUP BY [Link]
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 [Link], SUM([Link]) 'Total_Sales',
COUNT(distinct [Link]) as 'Resellers'
FROM FactResellerSalesXL_CCI f
INNER JOIN [Link] d ON [Link]= [Link]
INNER JOIN [Link] s on [Link]=[Link]
INNER JOIN [Link] e on [Link]=[Link]
WHERE FullDateAlternateKey between '1/1/2015' and '1/1/2017'
GROUP BY [Link]
ORDER BY Total_Sales
SET STATISTICS IO OFF
SET STATISTICS TIME OFF;

-- Example 27.8
USE sample;
SELECT * INTO FactInternetSales
FROM [Link];
GO
INSERT INTO FactInternetSales
SELECT * FROM [Link];
GO 6

-- Example 27.9
USE sample;
SELECT * INTO DimCustomer
FROM [Link];
GO
SELECT * INTO DimDate
FROM [Link];

-- Example 27.10
USE sample;
CREATE NONCLUSTERED COLUMNSTORE INDEX CLI_CS_IFactInternetSales
ON [Link](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 [Link], [Link],
SUM([Link]) TotalSales
FROM [Link] as f
INNER JOIN [Link] as c ON
[Link] = [Link]
INNER JOIN [Link] d ON
[Link] = [Link]
GROUP BY [Link], [Link];

You might also like