-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLSkills.com
-- For "More than Just Training," see the Industry Experts at 
-- 	http://www.SolidQualityLearning.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SET STATISTICS IO ON
-- Turn Graphical Showplan ON (Ctrl+K)

USE CREDIT
go

--Here's the final comparisons query
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge WITH (index (0)) -- table scan
GROUP BY member_no
ORDER BY member_no
OPTION (EXPAND VIEWS) -- this is to force sql server to go to the base table
go

-- CREATE INDEX Covering1 on charge(charge_amt, member_no) 
-- Not in order by the GROUP BY 
-- This is NOT ideal but it's better than the Table Scan
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge WITH (index (Covering1))
GROUP BY member_no
ORDER BY member_no
OPTION (EXPAND VIEWS)
go

--CREATE INDEX Covering2 on charge(member_no, charge_amt) -- In Order by the Group By 
-- In order of the GROUP BY... IDEAL - if you're going to have indexes.
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge WITH (index (Covering2))
GROUP BY member_no
ORDER BY member_no
OPTION (EXPAND VIEWS)
go

-- IF the Index has been created on the view then the final query
-- will require no hints, etc. This is most certainly the ideal plan! 
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge
GROUP BY member_no
ORDER BY member_no
go
