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

USE Credit
go

-- Create an interesting covering index
CREATE INDEX MemberNameIndex 
	ON Member(LastName, FirstName, MiddleInitial)
go
SELECT LastName, FirstName, MiddleInitial
FROM Member
-- NO WHERE Clause
-- Results In Table Scan
go
SELECT LastName, FirstName, MiddleInitial
FROM Member
WHERE LastName > ''
-- Index Seek (even though it's really a LEAF SCAN of the NC Index!)
go
--Compare the two 
-- Highlight both and execute with Execution Plan On

-- This is a bug in RTM/SP1/SP2/SP3...will need to test 
-- in sp4. SQL Server may not consider a covering index 
-- when a WHERE clause doesn't exist. But if you can 
-- come up with an interesting hint toward an index then 
-- it might actually help. This is your best type of HINT.
-- TRY to avoid forcing indexes!

-- DROP INDEX Member.MemberNameIndex 
