-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Written by Kimberly L. Tripp - all rights reserved.
-- 
-- For more scripts and sample code, check out 
-- 	http://www.SQLSkills.com
--
-- Disclaimer - Thoroughly test this script, execute at your own risk.
-- ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

BEGIN TRAN
sql
Q1 = select count(*) from tname where country = 'USA' --100
sql
sql
sql
sql
Q2 = select count(*) from tname where country = 'USA'
sql
COMMIT TRAN

Isolation Level
0	Read Uncommitted = NOLOCK = Dirty Reads
	Q1 > Q2, Q2 > Q1 or Q1 = Q2 - no guarantee
	Could even read data that has not yet been committed
1	Read Committed (DEFAULT)
	Q1 > Q2, Q2 > Q1 or Q1 = Q2 
	guarantee only committed values are shown
	(also known as Inconsistent Analysis Problem)
2	Repeatable Reads 
	solves the inconsistent analysis problem
	Q1 <= Q2 
	halloween problem
	problem is called a phantom when Q2 > Q1 
	new records introduced into set
3	Serializable = HOLDLOCK
	Q1 = Q2


--Setting these in a query on a table by table basis
select t.title, p.pub_name
from dbo.titles as t WITH (READUNCOMMITTED)
	inner join dbo.publishers as p WITH (HOLDLOCK)
		ON p.pub_id = t.pub_id

-- Setting these for a session

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED -- 0
SET TRANSACTION ISOLATION LEVEL READ COMMITTED -- 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ -- 2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE -- 3

-- Using READPAST
-- GO TO ANOTHER WINDOW AND RUN THIS - leaving the tran OPEN...
-- BEGIN TRAN
-- UPDATE authors SET au_lname = 'test' WHERE au_id = '722-51-5454'

SELECT * FROM pubs.dbo.authors --- this will be blocked
SELECT * FROM pubs.dbo.authors WITH (READPAST) --- this will NOT be blocked

-- Don't forget to rollback your tran
-- ROLLBACK TRAN

-- Join hints
select t.title, p.pub_name
from dbo.titles as t WITH (NOLOCK)
	inner loop join dbo.publishers as p WITH (HOLDLOCK)
		ON p.pub_id = t.pub_id
select t.title, p.pub_name
from dbo.titles as t WITH (NOLOCK)
	inner hash join dbo.publishers as p WITH (HOLDLOCK)
		ON p.pub_id = t.pub_id
select t.title, p.pub_name
from dbo.titles as t WITH (NOLOCK)
	inner merge join dbo.publishers as p WITH (HOLDLOCK)
		ON p.pub_id = t.pub_id

-- Expand Views - tells use base tables (even when index on view use base tables)
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge WITH (index (charge_charge_amt))
GROUP BY member_no
OPTION (EXPAND VIEWS)

-- NOEXPAND (notice it goes in the FROM clause) 
-- tells SQL Server to use indexed views 
-- (required hint on non-enterprise/developer edition systems)
SELECT member_no AS MemberNo, 
	sum(charge_amt) AS TotalSales
FROM dbo.charge WITH (NOEXPAND)
GROUP BY member_no

-- NOEXPAND - tells sql use indexes on views (required ON standard edition)
SELECT * 
FROM dbo.SumOfAllChargesByMember  WITH (NOEXPAND)
order by memberno


-- Few tips on Index Hints
-- FROM dbo.charge WITH (index (2)) -- number is supported in either syntax
-- FROM dbo.charge WITH (index = 2) -- again might break in Yukon
-- FROM dbo.charge (index = charge_charge_amt) -- old syntax MIGHT break in Yukon

-- 0 for Table Scan
-- 1 for CLustered index (seek or Scan)
-- For NC indexes use the name

-- For Index Intersection use multiple indexes and list the more selective one FIRST
-- WITH (INDEX(ind1, ind2))

-- If you want LOCK and INDEX Hints
-- WITH (INDEX(ind1, ind2), NOLOCK)

-- What about forceplan?
USE pubs
go
--SET FORCEPLAN OFF
select au_lname + ',' + substring(au_fname, 1, 1) + '.'
		AS 'Author''s Name'
	, title
	, pub_name
from titleauthor as ta 
	RIGHT OUTER JOIN authors as a
		on a.au_id = ta.au_id
	LEFT OUTER JOIN titles as t 
		on t.title_id = ta.title_id	
	LEFT OUTER JOIN publishers as p 
		on p.pub_id = t.pub_id	
--OPTION (FORCE ORDER)
--where type = 'business'
go
select au_lname + ',' + substring(au_fname, 1, 1) + '.'
		AS 'Author''s Name'
	, title
	, pub_name
from titleauthor as ta 
	inner join titles as t 
		on t.title_id = ta.title_id	
	RIGHT OUTER join authors as a
		on a.au_id = ta.au_id
	LEFT OUTER JOIN publishers as p 
		on p.pub_id = t.pub_id	
--where type = 'business'
go
select au_lname + ',' + substring(au_fname, 1, 1) + '.'
		AS 'Author''s Name'
	, title
	, pub_name
from authors as a
	LEFT OUTER join titleauthor as ta 
		on a.au_id = ta.au_id
	LEFT OUTER join titles as t 
		on t.title_id = ta.title_id	
	LEFT OUTER JOIN publishers as p 
		on p.pub_id = t.pub_id	
--where type = 'business'
go
select au_lname + ',' + substring(au_fname, 1, 1) + '.'
		AS 'Author''s Name'
	, title
	, pub_name
from titles as t 
	JOIN publishers as p 
		on p.pub_id = t.pub_id	
	inner join titleauthor as ta 
		on t.title_id = ta.title_id	
	RIGHT OUTER join authors as a
		on a.au_id = ta.au_id
--where type = 'business'
go
--NOT POSSIBLE TO WRITE INNER JOINS IN THIS ORDER
--titles -> authors -> titleauthor
--authors -> titles -> titleauthor

-- Parallelism MAX DOP (Degree of Parallelism)
-- FOR Server
-- sp_configure

-- OR for a query
OPTION(MAXDOP 1)