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

-- Use * to force the data retrieval to base table and choose a narrow NC Index
-- Estimate the point... 
-- First, figure out the number of pages in (CLUSTERED INDEX) a table scan by
-- dividing the space usage for data by 8.
exec sp_spaceused charge, true  -- the true is so that sql server doesn't lie :)
-- SELECT data/8 = pages
go
-- ??? Pages in Scan
-- When number of rows > pages for sure
-- When number of rows * levels > pages for sure 
	-- You won't know the levels so take 1/2, 1/3, 1/4 thinking....
	-- SELECT 583/2 = 291 -- this is likely the number of levels
	-- SELECT 583/3 = 194
-- Probably even a bit less... 
SELECT * FROM dbo.Charge
WHERE member_no < 100 -- 6 rows
-- 19 IOs INDEX SEEK (NC Index to Clustered through Bookmark Lookup)
go
SELECT * FROM dbo.Charge
WHERE member_no < 200 -- 23 rows
-- 57 IOs INDEX SEEK (NC Index to Clustered through Bookmark Lookup)
go
SELECT * FROM dbo.Charge
WHERE member_no < 500 -- 138 rows
-- 583 IOs TABLE SCAN
go
-- So where's the cutoff?
SELECT * FROM dbo.Charge
WHERE member_no < 275 -- 46 rows
-- 105 IOs INDEX SEEK (NC Index to Clustered through Bookmark Lookup)
go
SELECT * FROM dbo.Charge
WHERE member_no < 276 -- 47 rows
-- 583 IOs TABLE SCAN
go

-- Compare these two with SHOWPLAN and you'll see percentages of
-- 49.97% vs 50.03% but the IOs are significantly different...
-- What's the difference? 

-- Sequential v. Random, per se.
go