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

-- DROP INDEX member.testind2

UPDATE Member
SET LastName = 'Tripp'
WHERE Member_No = 6798
go

UPDATE Member
SET FirstName = 'Kimberly'
WHERE Member_No = 2896
go

UPDATE Member
SET FirstName = 'Kimberly',
	LastName = 'Tripp'
WHERE Member_No = 842
go

CREATE INDEX MemberLastName ON Member(LastName)
CREATE INDEX MemberFirstName ON Member(FirstName)
go
drop index member.membercovering
SET STATISTICS IO ON
go

SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName = 'Kimberly'
	OR m.LastName = 'Tripp'
go
CREATE INDEX Member1 ON dbo.member(firstname, lastname, region_no)
CREATE INDEX Member2 ON dbo.member(lastname, firstname, region_no)

SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
go
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
	OR m.LastName LIKE 'Tripp'
go

SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
UNION
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go

SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
UNION ALL
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go

-- IS UNION always the same as OR...NO!
-- UNION removes dupes based on SELECT LIST
-- OR removes dupes based on ROWID (OR unique row identifier)

-- Let's update more rows...
UPDATE member
    SET firstname = 'Kimberly',
            lastname = 'Tripp'
    WHERE member_no % 19 = 0

UPDATE member
    SET firstname = 'Kimberly'
    WHERE member_no % 17 = 0
go

SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
	OR m.LastName LIKE 'Tripp'
go

SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
UNION
SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go

SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
UNION ALL
SELECT m.LastName, m.FirstName, m.Region_No
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go

-- How about adding in the Primary Key
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
	OR m.LastName LIKE 'Tripp'
go

SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
UNION
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go

-- Remember that UNION ALL does NOT guarantee an identical
-- result set BUT if you
--	Don't care about dupes
--	OR 
--	You know your data REALLY well...
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.FirstName LIKE 'Kimberly'
UNION ALL
SELECT m.LastName, m.FirstName, m.Region_No, m.member_no
FROM dbo.Member AS m
WHERE m.LastName LIKE 'Tripp'
go
