-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp, SQLskills.com
-- All Rights Reserved.
-- 
-- For more scripts, sample code and additional training resources,
-- go to http://www.SQLskills.com
--	Immerse yourself in SQL Server
--
-- 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
