Practice #5

First we create to table, Product that contains

ProductID, Name, ProductNumber, StockLevel, ListPrice

and ProductInventory that contains

ProductID, LocationID, Shelf, Bin, Quantity, rowguid, ModifiedDate

Then turn the statistics io into on

SET STATISTICS IO ON;

First, try to query some data

SELECT p.Name, p.ListPrice, i.Shelf, i.Bin, i.Quantity
FROM Product as p
JOIN ProductInventory as i
ON p.ProductID = i.ProductID
WHERE p.Name = -- somestring

Now we are going to apply the Columnstore Index into our table

CREATE CLUSTERED COLUMNSTORE INDEX idx_ProductInventory 
ON ProductInventory

Try re-run the query

SELECT p.Name, p.ListPrice, i.Shelf, i.Bin, i.Quantity
FROM Product as p
JOIN ProductInventory as i
ON p.ProductID = i.ProductID
WHERE p.Name = -- somestring

Now we add another Indexing system using Nonclustered Columnstore

CREATE NONCLUSTERED COLUMNSTORE INDEX idx_Product ON Product(ProductID, Name, ListPrice)

Now try again to run the query statement

SELECT p.Name, p.ListPrice, i.Shelf, i.Bin, i.Quantity
FROM Product as p
JOIN ProductInventory as i
ON p.ProductID = i.ProductID
WHERE p.Name = -- somestring