/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

Option (RECOMPILE)
2012-05-16
*****************************************************/
set nocount on
go

create table dbo.Customers
(
	CustomerId int not null,
	CustomerType varchar(32) not null,
	Name nvarchar(64) not null,
	Address nvarchar(128) not null,
	City nvarchar(32) not null,
	Region nvarchar(32) not null,
	PostalCode nvarchar(20) not null,
	Country nvarchar(32) not null,
	Phone varchar(32) not null,
	BusinessContact nvarchar(64) null,
	BusinessVertical nvarchar(32) null,
	Placeholder char(200) not null
		constraint DEF_Customers_Placeholder
		default 'This is placeholder',
		
	constraint PK_Customers
	primary key clustered(CustomerId)
)
go

;with CTE(ID)
as
(
	select 1
	union all
	select ID + 1
	from CTE
	where ID < 100000  
)    
insert into dbo.Customers(CustomerId, CustomerType, Name,
	Address, City, Region, PostalCode, Country, Phone, 
	BusinessContact, BusinessVertical)
	select Id, 'BUSINESS', 'BUSINESS: ' + convert(varchar(12),ID)
		,'Address','City','Region','PostalCode',
		case when ID % 100 = 0 then 'Canada' else 'USA' end,
		'123-456-7890','Business Contact', 'Vertical'
	from CTE
option (maxrecursion 0)
go

;with CTE(ID)
as
(
	select 200000
	union all
	select ID + 1
	from CTE
	where ID < 220000  
)    
insert into dbo.Customers(CustomerId, CustomerType, Name,
	Address, City, Region, PostalCode, Country, Phone)
	select Id, 'CONSUMER', 'CONSUMER: ' + convert(varchar(12),ID)
		,'Address','City','Region','PostalCode',
		case when ID % 100 = 0 then 'Canada' else 'USA' end,
		'123-456-7890'
	from CTE
option (maxrecursion 0)
go

select country, count(*)
from dbo.Customers
group by Country
go

create index IDX_Customers_Country
on dbo.Customers(Country)
go


-- Parameter Sniffing
set statistics io on
go

exec sp_executesql
	N'select * from dbo.Customers where Country = @Country'
	,N'@Country nvarchar(32)'
	,@Country = 'Canada'
go

exec sp_executesql
	N'select * from dbo.Customers where Country = @Country'
	,N'@Country nvarchar(32)'
	,@Country = 'USA'
go

select * from dbo.Customers where Country = 'USA'
go


exec sp_executesql
	N'select * from dbo.Customers where Country = @Country option (recompile)'
	,N'@Country nvarchar(32)'
	,@Country = 'Canada'
go

exec sp_executesql
	N'select * from dbo.Customers where Country = @Country option (recompile)'
	,N'@Country nvarchar(32)'
	,@Country = 'USA'
go

create proc dbo.GetCustomerByCountry @Country nvarchar(32)
as	
	select * 
	from dbo.Customers 
	where Country = @Country
go

exec dbo.GetCustomerByCountry @Country = 'USA'
exec dbo.GetCustomerByCountry @Country = 'Canada'
go

create proc dbo.GetCustomerByCountry2 @Country nvarchar(32)
as	
	select * 
	from dbo.Customers 
	where Country = @Country
	option (recompile)
go

exec dbo.GetCustomerByCountry2 @Country = 'USA'
exec dbo.GetCustomerByCountry2 @Country = 'Canada'
go

-- Optional parameters
create proc dbo.GetCustomerByCountry3
(
	@Country nvarchar(32) = null
)  
as	
	select * 
	from dbo.Customers 
	where @Country is null or Country = @Country
go

exec dbo.GetCustomerByCountry3 
exec dbo.GetCustomerByCountry3 @Country = 'Canada'
go

create proc dbo.GetCustomerByCountry4
(
	@Country nvarchar(32) = null
)  
as	
	select * 
	from dbo.Customers 
	where @Country is null or Country = @Country
	option (recompile)
go


exec dbo.GetCustomerByCountry4
exec dbo.GetCustomerByCountry4 @Country = 'Canada'
go

-- Filtered indexes

create index IDX_Customers_Name_Phone_ForConsumers
on dbo.Customers(Name, Phone)
where CustomerType = 'CONSUMER'
go

create index IDX_Customers_Name_BusinessContact_Phone_ForBusiness
on dbo.Customers(Name, BusinessContact, Phone)
where CustomerType = 'BUSINESS'
go

create proc dbo.GetCustomerByNameAndType 
(
	@Name nvarchar(64)
	,@CustomerType varchar(32)
)
as
	select *
	from dbo.Customers
	where 
		CustomerType = @CustomerType and
		Name = @Name
go

exec dbo.GetCustomerByNameAndType 
	@Name='Business: 10'
	,@CustomerType = 'BUSINESS'

exec dbo.GetCustomerByNameAndType 
	@Name='Consumer: 200005'
	,@CustomerType = 'CONSUMER'
go

create proc dbo.GetCustomerByNameAndType2 
(
	@Name nvarchar(64)
	,@CustomerType varchar(32)
)
as
	select *
	from dbo.Customers
	where 
		CustomerType = @CustomerType and
		Name = @Name
	option (recompile)
go

exec dbo.GetCustomerByNameAndType2 
	@Name='Business: 10'
	,@CustomerType = 'BUSINESS'

exec dbo.GetCustomerByNameAndType2 
	@Name='Consumer: 200005'
	,@CustomerType = 'CONSUMER'
go
