Performance

use ReportBuilderTraining
go


if object_id('evc.Student1') is not null
   	drop table evc.Student1

if object_id('evc.Student2') is not null
   	drop table evc.Student2

go

select StudentId, FirstName, LastName, Email
	into evc.Student1
	from Student

select StudentId, FirstName, LastName, Email
	into evc.Student2
	from Student


--
-- Compare performance of searching in ID with and without primary key
--

alter table evc.Student1 add constraint PK_tmp_Student1 primary key (StudentId)

go

select *
	from evc.Student1
	where StudentId = 2

select *
	from evc.Student2
	where StudentId = 2

go


---
--- does order of index elements matter?
---

alter table evc.Student2 add constraint PK_evc_Student2 primary key (StudentId)

create nonclustered index idx_nc_K2_K3 on evc.Student1 (FirstName, LastName)
create nonclustered index idx_nc_K3_K2 on evc.Student2 (LastName, FirstName)
go


select FirstName, LastName from evc.Student1 where LastName='Kelly'

select FirstName, LastName from evc.Student2 where LastName='Kelly'
go

select FirstName, LastName from evc.Student1 where LastName='Kelly' and FirstName='Robert'

select FirstName, LastName from evc.Student2 where LastName='Kelly' and FirstName='Robert'
go


---
--- See effect of INCLUDE
---

-- we get the PK for free, it is implicetly included in teh nonclusted indexes we created

select StudentId, FirstName, LastName from evc.Student1 where LastName='Kelly' and FirstName='Robert'

select StudentId, FirstName, LastName from evc.Student2 where LastName='Kelly' and FirstName='Robert'
go


-- but Email is not in the index, so we have to "join" back to the mail table to retrieve it

select StudentId, FirstName, LastName, Email from evc.Student1 where LastName='Kelly' and FirstName='Robert'

select StudentId, FirstName, LastName, Email from evc.Student2 where LastName='Kelly' and FirstName='Robert'
go


-- or could "include" Email in the index for much better performance

create nonclustered index idx_nc_K2_K3_1 on evc.Student1 (FirstName, LastName) include (Email)
go


select StudentId, FirstName, LastName, Email from evc.Student1 where LastName='Kelly' and FirstName='Robert'

select StudentId, FirstName, LastName, Email from evc.Student2 where LastName='Kelly' and FirstName='Robert'
go




---
--- Effect of functions and data type on use of indexes
---

select AddressId
	from Address
	where (Street1 is null or Street1 = '')

select AddressId
	from Address
	where isnull(Street1, '') = ''

select AddressId
	from Address
	where (Street1 is null or Street1 = N'')