Many-To-Many 2

/*

create two sets of tables which are identical except one set does not have foriegn keys defined.

This file can be used to evaluate if the foreign keys improve query performance (are they used as indexes?)

*/


use ReportBuilderTraining

---
--- cleanup from last run
---

-- need to delete this first because of the foreign keys
if object_id('evc.ReportCategory') is not null
   	drop table evc.ReportCategory
	
if object_id('evc.Report') is not null
   	drop table evc.Report
 
if object_id('evc.Category') is not null
   	drop table evc.Category

go

-- need to delete this first because of the foreign keys
if object_id('evc.Report2Category2') is not null
   	drop table evc.Report2Category2
	
if object_id('evc.Report2') is not null
   	drop table evc.Report2
 
if object_id('evc.Category2') is not null
   	drop table evc.Category2


go

---
--- Create tables
---

create table evc.Report (
	 ReportID int not null 
	,Name varchar(50) null
	,constraint evc_Report_PK primary key clustered (ReportID)
)

create table evc.Category (
	 CategoryID int not null 
	,Name varchar(50) null
	,constraint evc_Category_PK primary key clustered (CategoryID)
)

create table evc.ReportCategory (
	 ReportId int not null
	,CategoryId int not null
	,constraint evc_ReportCategory_PK primary key clustered (ReportId, CategoryId)
)

go



---
--- Create tables
---

create table evc.Report2 (
	 ReportID int not null 
	,Name varchar(50) null
	,constraint evc_Report2_PK primary key clustered (ReportID)
)

create table evc.Category2 (
	 CategoryID int not null 
	,Name varchar(50) null
	,constraint evc_Category2_PK primary key clustered (CategoryID)
)

create table evc.Report2Category2 (
	 ReportId int not null
	,CategoryId int not null
	,constraint evc_ReportCategory2_PK primary key clustered (ReportId, CategoryId)
)

go

---
--- Add foreign keys (only on the first set of tables)
---

alter table evc.ReportCategory 
	add foreign key (ReportID) 
	references evc.Report(ReportId) 
	on delete cascade 
	on update cascade

alter table evc.ReportCategory 
	add foreign key (CategoryID) 
	references evc.Category(CategoryID) 
	on delete cascade 
	on update cascade

go


---
--- Create data
---

insert into evc.Report (ReportId, Name) values (1, 'Enrollment Counts of Diverse Students')
insert into evc.Report (ReportId, Name) values (2, 'Graduation Counts of Diverse Student')
insert into evc.Report (ReportId, Name) values (3, 'Enrollment Treds')
insert into evc.Report (ReportId, Name) values (4, 'Graduation Trends')

insert into evc.Category (CategoryId, Name) 
	values 
		 (1, 'Enrolled Students') 
		,(2, 'Graduates')
		,(3, 'Students At Risk')


go

select * from evc.Report
select * from evc.Category

insert into evc.ReportCategory (ReportId, CategoryId) 
	values 
		 (1, 1)
		,(1, 3)
		,(2, 2)
		,(2, 3)
		,(3, 1)
		,(4, 2)

go


---
--- Create data
---

insert into evc.Report2 (ReportId, Name) values (1, 'Enrollment Counts of Diverse Students')
insert into evc.Report2 (ReportId, Name) values (2, 'Graduation Counts of Diverse Student')
insert into evc.Report2 (ReportId, Name) values (3, 'Enrollment Treds')
insert into evc.Report2 (ReportId, Name) values (4, 'Graduation Trends')

insert into evc.Category2 (CategoryId, Name) 
	values 
		 (1, 'Enrolled Students') 
		,(2, 'Graduates')
		,(3, 'Students At Risk')


go

select * from evc.Report2
select * from evc.Category2

insert into evc.Report2Category2 (ReportId, CategoryId) 
	values 
		 (1, 1)
		,(1, 3)
		,(2, 2)
		,(2, 3)
		,(3, 1)
		,(4, 2)

go


---
--- join the two different sets of tables and see if performance if different.
---


select c.Name Category, r.Name Report
	from evc.Category c
	join evc.ReportCategory rc
	on c.CategoryID=rc.CategoryId
	join evc.Report r
	on rc.ReportId=r.ReportID
	order by 1, 2


select c.Name Category, r.Name Report
	from evc.Category2 c
	join evc.Report2Category2 rc
	on c.CategoryID=rc.CategoryId
	join evc.Report2 r
	on rc.ReportId=r.ReportID
	order by 1, 2




---
--- what if I only need results from one table?
---
--- see http://www.scarydba.com/2010/11/22/do-foreign-key-constraints-help-performance/
--- and http://www.scarydba.com/2015/09/09/yes-foreign-keys-help-performance/
---

select r.Name Report
	from evc.Category c
	join evc.ReportCategory rc
	on c.CategoryID=rc.CategoryId
	join evc.Report r
	on rc.ReportId=r.ReportID
	--where c.CategoryId = 1
	order by 1


select r.Name Report
	from evc.Category2 c
	join evc.Report2Category2 rc
	on c.CategoryID=rc.CategoryId
	join evc.Report2 r
	on rc.ReportId=r.ReportID
	--where c.CategoryId = 1
	order by 1