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

-- First create these single column indexes
CREATE INDEX MemberLastName ON Member(LastName)
CREATE INDEX MemberFirstName ON Member(FirstName)
go

-- Review your index list
EXEC sp_helpindex member
go
-- Or use the system tables...
SELECT * FROM sysindexes WHERE id = object_id('member') 
go

-- Check space usage
EXEC sp_spaceused member, TRUE
-- DBCC UPDATEUSAGE
--sp_helptext sp_spaceused 
go

SET STATISTICS IO ON
go

-- WITH ONLY the indexes created as above this query does
-- as best it can by joining two indexes to cover the query!
-- Great but not as optimal as it could be?!

SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'K%'   -- guess 1/26 of table
        AND m.Region_No > 6 -- guess 1/3 of table
        AND m.Member_No < 5000  -- 1/2 of table
--index fname, member_no 1/26 * 1/2 = 1/52
--index region_no, member_no 1/3 * 1/2 = 1/6

-- What if we could only use one index? Would it be worthwhile?
-- NO BUT prior to 7.0 SQL Server could only use one index per table
-- per query...these two queries are worse than a table scan!
SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (index (MemberFirstName))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (index (member_region_link))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

-- SO - using two indexes is certainly a better choice!!! If you ever 
-- want to force multiple indexes, the index order IS IMPORTANT.
-- Put the more selective index first!
-- Compare these two and you'll see the first is faster!
SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (index (MemberFirstName, member_region_link))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m WITH (index (member_region_link, MemberFirstName))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

-- BUT the best choice for indexing for AND is...
-- (1) Create an index on ANY very selective column. If something 
-- is very selective then that's the only index you need!
-- (2) Create in index on ANY combination of selective criteria. You
-- don't need everything from the WHERE clause but try and supply
-- the combination of columns that will yield a selective set!
-- (3) If nothing is selective - even when combined - then cover
-- the query!

CREATE INDEX MemberCovering ON member(firstname, region_no, member_no)
-- IS identical to...
-- CREATE INDEX MemberCovering ON member(firstname, region_no) -- member_no is CL
-- because the member_no column is the clustered index 
-- and therefore already included in the index.

SELECT m.Member_No, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  
go

-- What if our index doesn't cover!
-- AND it's very low selectivity THEN the best index might be 
-- NOT to use one...TABLE SCAN
SELECT m.Member_No, m.FirstName, m.Region_No, m.phone_no
FROM dbo.Member AS m 
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

SELECT m.Member_No, m.FirstName, m.Region_No, m.phone_no
FROM dbo.Member AS m WITH (index (MemberFirstName))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

SELECT m.Member_No, m.FirstName, m.Region_No, m.phone_no
FROM dbo.Member AS m WITH (index (member_region_link))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

-- When forcing multiple indexes - order may be relevant
SELECT m.Member_No, m.FirstName, m.Region_No, m.phone_no
FROM dbo.Member AS m WITH (index (MemberFirstName, member_region_link))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

SELECT m.Member_No, m.FirstName, m.Region_No, m.phone_no
FROM dbo.Member AS m WITH (index (member_region_link, MemberFirstName))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

SELECT m.Member_No, m.FirstName, m.Region_No, m.phone_no
FROM dbo.Member AS m WITH (index (MemberCovering))
WHERE m.FirstName LIKE 'K%'   
        AND m.Region_No > 6 
        AND m.Member_No < 5000  

-- DROP INDEX Member.MemberLastName
-- DROP INDEX Member.MemberFirstName
-- DROP INDEX Member.MemberCovering