set statistics time on set statistics io on dbcc freeproccache dbcc dropcleanbuffers --Covering index (Q1) --1 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 49 and 50 --2 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 49 and 50 --3 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 1293 and 1294 --4 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 1293 and 1294 --5 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 2488 and 2495 --6 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 2488 and 2495 --7 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 2488 and 2517 --8 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 2488 and 2517 --9 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 1700 and 2517 --10 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 1700 and 2517 --11 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 1500 and 2517 --12 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 1500 and 2517 --13 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where productkey between 500 and 2517 --14 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where productkey between 500 and 2517 --INDEX SCANS (Q2) --15 SELECT Customerkey,Count(1) FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where Customerkey between 9893 and 9934 GROUP BY Customerkey --16 SELECT Customerkey,Count(1) FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where Customerkey between 9893 and 9934 GROUP BY Customerkey --17 SELECT Customerkey,Count(1) FROM [ContosoRetailDW].[dbo].[FactOnlineSales] where Customerkey between 9000 and 9934 GROUP BY Customerkey --18 SELECT Customerkey,Count(1) FROM [ContosoRetailDW].[dbo].[FactOnlineSales_CI] where Customerkey between 9000 and 9934 GROUP BY Customerkey --Indexes on Hybrid tables (Q3) --19 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_BT_CI] where productkey between 49 and 50 --20 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_BT_CI] where productkey between 2488 and 2495 --21 SELECT [ProductKey],[CustomerKey],[SalesOrderNumber] ,[SalesQuantity] ,[SalesAmount] ,[DiscountQuantity],[DiscountAmount] FROM [ContosoRetailDW].[dbo].[FactOnlineSales_BT_CI] where productkey between 1500 and 2517