sql server - Foreign Key Column as a covering index -

a company contract has legacy set of tables use soft-deletes , interesting table structuring. joins pk-->fk relationships, issue comes many different queries accessing different columns.

the write rate low, , read rate high. so, lead me believe creating multiple covering indices on multiple child columns utilized queries not pose risk on performance having single index. disk space aside, best suggestion child table facilityvisit index (indices).

here’s simplified example (some of tables have 25 cols queried different queries):

create table person (  personid int identity (1,1)  ,firstname varchar(100) ,lastname varchar(100) constraint [pk_person_personid] primary key clustered (personid) ) go  create table facilityvisit (  facilityvisitid int identity (1,1) ,personid int ,visitdatetime datetime ,secionvisited int ,scoregiven int ,[status] bit constraint [pk_facilityvisit_facilityvisitid] primary key clustered (facilityvisitid) ) alter table [dbo].[facilityvisit]  check     add constraint [fk_facilityvisit_personid] foreign key([personid]) references [dbo].[person] ([personid]) go  /* possible logical indexes facilityvisit */  create nonclustered index fk_facilityvisit_cindex1       on facilityvisit ([personid]) --or create nonclustered index fk_facilityvisit_cindex2       on facilityvisit ([personid],[visitdatetime],[status])  --or  create nonclustered index fk_facilityvisit_cindex3       on facilityvisit ([personid],[visitdatetime],[status])       include (sectionvisited,scoregiven) 

possible queries:

/* best utilzie fk_facilityvisit_cindex1 */ select   p.firstname ,p.lastname person p join facilityvisit fv on fv.personid = p.personid  /* better utilzie fk_facilityvisit_cindex2 */ select   p.firstname ,p.lastname person p join facilityvisit fv on fv.personid = p.personid fv.status = 1 , fv.visitdatetime >= '01/01/2015' , fv.visitdatetime <= '12/31/2015'  /* best utilzie fk_facilityvisit_cindex3 */ select   p.firstname ,p.lastname ,fv.visitdate ,fv.sectionvisited ,fv.scoregiven person p join facilityvisit fv on fv.personid = p.personid fv.status = 1 , fv.visitdatetime >= '01/01/2015' , fv.visitdatetime <= '12/31/2015' 

firstly remember this, measure twice optimise once... whenever want optimise must measure before , after put in optimisation, , if doesn't give significant enough optimisation, or makes slower! undo optimisation.

secondly if not adept @ reading query execution plans have @ them queries running, sql server recommend indexes thinks help, again measure change make sure. fantastic way find out should try optimising.

now of 3 index examples, i'd pick last 1 slight modification:

create nonclustered index fk_facilityvisit_cindex3   on facilityvisit ([personid],[status],[visitdatetime])   include (sectionvisited,scoregiven) 

all of 3 possible queries use index. changing order of key fields narrow search down single range within key meaning can searched using index seek. if didn't filter personid index unlikely used.

finally, want covering index, have 2 tables inner join, , specifying columns: create view , add covering index this:

create personfacilityvisit schemabinding  select       p.personid     ,p.firstname     ,p.lastname     ,fv.facilityvisitid     ,fv.visitdatetime     ,fv.secionvisited     ,fv.scoregiven dbo.person p join dbo.facilityvisit fv on fv.personid = p.personid status = 1 go  create unique clustered index pk_personvisit_view     on personfacilityvisit (personid, facilityvisitid)  create index ix_personvisit_view       on personfacilityvisit ([visitdatetime],[personid])       include (firstname,lastname,secionvisited,scoregiven) 

you can read more creating indexed views here.