-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 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

--Original Query
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge 	-- with no useful indexes this does a table scan 
					-- and hash aggregate
GROUP BY member_no
-- Run this once withOUT the order by... HASH aggregates do NOT return
-- the data ordered. You MUST add order by if you want the data ordered.
ORDER BY member_no -- you probably want the data ordered?
go

CREATE INDEX Covering1 on dbo.charge(charge_amt, member_no) 
	-- Not in order by the Group By 
go

-- Run the query again - you'll see HASH AGGREGATE but on a narrower
-- set... When you compare these you'll see that this is faster.
go

SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge
GROUP BY member_no
ORDER BY member_no -- TO be fair in the comparison you should all the ORDER BY
go

CREATE INDEX Covering2 on charge(member_no, charge_amt) -- In Order by the Group By 
go

-- Run the query again - you'll see STREAM
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge
GROUP BY member_no
ORDER BY member_no -- TO be fair in the comparison you should all the ORDER BY
						-- BUT because this is a stream aggregate you'll see NO
						-- additional costs are added to the aggregate.
go

-- DROP ALL Indexes Added for this Demo
-- DROP INDEX Charge.Covering1
-- DROP INDEX Charge.Covering2
