
use Credit
go

-- Update a row to later search on...
UPDATE dbo.member
	SET lastname = 'Tripp'
		WHERE member_no = 1234
go
-- Review the indexes to see if there's an index for LastName
EXEC sp_helpindex 'dbo.member'
go

-- Create an index on LastName ALONE
CREATE INDEX MemberLastName ON dbo.member(LastName)
go

DROP PROCEDURE GetMemberInfo
GO
CREATE PROCEDURE GetMemberInfo
( 	@LastName	varchar(30) )
AS
SELECT m.* FROM dbo.Member AS m
WHERE m.LastName LIKE @LastName
go

EXEC dbo.GetMemberInfo 'Tripp' 
EXEC dbo.GetMemberInfo 'T%'    
EXEC dbo.GetMemberInfo '%T%' 

CREATE PROCEDURE GetMemberInfo2
( 	@LastName	varchar(30) )
	WITH RECOMPILE
AS
SELECT m.* FROM dbo.Member AS m
WHERE m.LastName LIKE @LastName
go

EXEC dbo.GetMemberInfo2 'Tripp' -- indexes + nested loops
EXEC dbo.GetMemberInfo2 'T%'    -- optimally a table scan
EXEC dbo.GetMemberInfo2 '%T%'   -- optimally a table scan!






