= Некластерирани индекси

[wiki:UnclusteredIndexVerification Верификација на некластерираните индекси]

Некластерираните индекси се избрани според најчестите операции:
==== O1: Времеплов на `encounters` по пациент

{{{#!sql
select *
from encounters
where patient = @patient
    and [start] >= @from and [start] < @to
order by [start];
go
}}}

==== O2: `observations` за еден `encounter`

{{{#!sql
select *
from observations
where encounter = @encounter
order by [date];
go
}}}

==== O3: `conditions` за еден пациент

{{{#!sql
select *
from conditions
where patient = @patient
order by [start];
go
}}}

==== O4: `medications` за пациент

{{{#!sql
select *
from medications
where patient = @patient
order by [start];
go
}}}

== `encounters`

{{{#!sql
create nonclustered index IX_Encounters_Patient_Start
on dbo.encounters(patient, [start])
include (stop, encounterclass, code, description, payer, provider, organization, base_encounter_cost, total_claim_cost, payer_coverage);
go
}}}

Овој индекс ја поддржува О1 - бара по пациент, опсег по `start`.

`include` избегнува пребарување по типични колони.

== `observations`

{{{#!sql
create nonclustered index IX_Observations_Encounters_Date
on dbo.observations(encounter, [date])
include (patient, category, code, description, value, units, type);
go
}}}

Овој индекс ја поддржува О2 - пребарува по encounter, подредува по датум.

== `conditions`

{{{#!sql
create nonclustered index IX_Conditions_Patient_Start
on dbo.conditions(patient, [start])
include (stop, encounter, system, code, description);
go

create nonclustered index IX_Conditions_Encounter
on dbo.conditions(encounter)
include (patient, [start], stop, system, code, description);
go
}}}

Првиот индекс ја поддржува О3.

Вториот индекс поддржува join-ови и филтри според encounter.

== `medications`

{{{#!sql
create nonclustered index IX_Medications_Patient_Start
on dbo.medications(patient, [start])
include (stop, payer, encounter, code, description, dispenses, base_cost, payer_coverage, totalcost, reasoncode, reasondescription);
go

create nonclustered index IX_Medications_Encounter
on dbo.medications(encounter)
include (patient, [start], stop, code, totalcost);
go
}}}

== `procedures`

{{{#!sql
create nonclustered index IX_Procedures_Encounter_Start
on dbo.procedures(encounter, [start])
include (patient, system, code, description, base_cost, reasoncode, reasondescription);
go
}}}