/*sql server OMOP CDM Indices
  There are no unique indices created because it is assumed that the primary key constraints have been run prior to
  implementing indices
*/
/************************
Standardized clinical data
************************/
CREATE INDEX idx_person_id ON PERSON (person_id ASC);
CREATE INDEX idx_gender ON PERSON (gender_concept_id ASC);
CREATE INDEX idx_observation_period_id_1 ON OBSERVATION_PERIOD (person_id ASC);
CREATE INDEX idx_visit_person_id_1 ON VISIT_OCCURRENCE (person_id ASC);
CREATE INDEX idx_visit_concept_id_1 ON VISIT_OCCURRENCE (visit_concept_id ASC);
CREATE INDEX idx_visit_det_person_id_1 ON VISIT_DETAIL (person_id ASC);
CREATE INDEX idx_visit_det_concept_id_1 ON VISIT_DETAIL (visit_detail_concept_id ASC);
CREATE INDEX idx_condition_person_id_1 ON CONDITION_OCCURRENCE (person_id ASC);
CREATE INDEX idx_condition_concept_id_1 ON CONDITION_OCCURRENCE (condition_concept_id ASC);
CREATE INDEX idx_condition_visit_id_1 ON CONDITION_OCCURRENCE (visit_occurrence_id ASC);
CREATE INDEX idx_drug_person_id_1 ON DRUG_EXPOSURE (person_id ASC);
CREATE INDEX idx_drug_concept_id_1 ON DRUG_EXPOSURE (drug_concept_id ASC);
CREATE INDEX idx_drug_visit_id_1 ON DRUG_EXPOSURE (visit_occurrence_id ASC);
CREATE INDEX idx_procedure_person_id_1 ON PROCEDURE_OCCURRENCE (person_id ASC);
CREATE INDEX idx_procedure_concept_id_1 ON PROCEDURE_OCCURRENCE (procedure_concept_id ASC);
CREATE INDEX idx_procedure_visit_id_1 ON PROCEDURE_OCCURRENCE (visit_occurrence_id ASC);
CREATE INDEX idx_device_person_id_1 ON DEVICE_EXPOSURE (person_id ASC);
CREATE INDEX idx_device_concept_id_1 ON DEVICE_EXPOSURE (device_concept_id ASC);
CREATE INDEX idx_device_visit_id_1 ON DEVICE_EXPOSURE (visit_occurrence_id ASC);
CREATE INDEX idx_measurement_person_id_1 ON MEASUREMENT (person_id ASC);
CREATE INDEX idx_measurement_concept_id_1 ON MEASUREMENT (measurement_concept_id ASC);
CREATE INDEX idx_measurement_visit_id_1 ON MEASUREMENT (visit_occurrence_id ASC);
CREATE INDEX idx_observation_person_id_1 ON OBSERVATION (person_id ASC);
CREATE INDEX idx_observation_concept_id_1 ON OBSERVATION (observation_concept_id ASC);
CREATE INDEX idx_observation_visit_id_1 ON OBSERVATION (visit_occurrence_id ASC);
CREATE INDEX idx_death_person_id_1 ON DEATH (person_id ASC);
CREATE INDEX idx_note_person_id_1 ON NOTE (person_id ASC);
CREATE INDEX idx_note_concept_id_1 ON NOTE (note_type_concept_id ASC);
CREATE INDEX idx_note_visit_id_1 ON NOTE (visit_occurrence_id ASC);
CREATE INDEX idx_note_nlp_note_id_1 ON NOTE_NLP (note_id ASC);
CREATE INDEX idx_note_nlp_concept_id_1 ON NOTE_NLP (note_nlp_concept_id ASC);
CREATE INDEX idx_specimen_person_id_1 ON SPECIMEN (person_id ASC);
CREATE INDEX idx_specimen_concept_id_1 ON SPECIMEN (specimen_concept_id ASC);
CREATE INDEX idx_fact_relationship_id1 ON FACT_RELATIONSHIP (domain_concept_id_1 ASC);
CREATE INDEX idx_fact_relationship_id2 ON FACT_RELATIONSHIP (domain_concept_id_2 ASC);
CREATE INDEX idx_fact_relationship_id3 ON FACT_RELATIONSHIP (relationship_concept_id ASC);
/************************
Standardized health system data
************************/
CREATE INDEX idx_location_id_1 ON LOCATION(location_id ASC);
CREATE INDEX idx_care_site_id_1 ON CARE_SITE(care_site_id ASC);
CREATE INDEX idx_provider_id_1 ON PROVIDER (provider_id ASC);
/************************
Standardized health economics
************************/
CREATE INDEX idx_period_person_id_1 ON PAYER_PLAN_PERIOD (person_id ASC);
CREATE INDEX idx_cost_event_id  ON COST (cost_event_id ASC);
/************************
Standardized derived elements
************************/
CREATE INDEX idx_drug_era_person_id_1 ON DRUG_ERA (person_id ASC);
CREATE INDEX idx_drug_era_concept_id_1 ON DRUG_ERA (drug_concept_id ASC);
CREATE INDEX idx_dose_era_person_id_1 ON DOSE_ERA (person_id ASC);
CREATE INDEX idx_dose_era_concept_id_1 ON DOSE_ERA (drug_concept_id ASC);
CREATE INDEX idx_condition_era_person_id_1 ON CONDITION_ERA (person_id ASC);
CREATE INDEX idx_condition_era_concept_id_1 ON CONDITION_ERA (condition_concept_id ASC);
/**************************
Standardized meta-data
***************************/
CREATE INDEX idx_metadata_concept_id_1 ON METADATA (metadata_concept_id ASC);
/**************************
Standardized vocabularies
***************************/
CREATE INDEX idx_concept_concept_id ON CONCEPT (concept_id ASC);
CREATE INDEX idx_concept_code ON CONCEPT (concept_code ASC);
CREATE INDEX idx_concept_vocabluary_id ON CONCEPT (vocabulary_id ASC);
CREATE INDEX idx_concept_domain_id ON CONCEPT (domain_id ASC);
CREATE INDEX idx_vocabulary_vocabulary_id ON VOCABULARY (vocabulary_id ASC);
CREATE INDEX idx_domain_domain_id ON DOMAIN (domain_id ASC);
CREATE INDEX idx_concept_class_class_id ON CONCEPT_CLASS (concept_class_id ASC);
CREATE INDEX idx_concept_relationship_id_1 ON CONCEPT_RELATIONSHIP (concept_id_1 ASC);
CREATE INDEX idx_concept_relationship_id_2 ON CONCEPT_RELATIONSHIP (concept_id_2 ASC);
CREATE INDEX idx_concept_relationship_id_3 ON CONCEPT_RELATIONSHIP (relationship_id ASC);
CREATE INDEX idx_relationship_rel_id ON RELATIONSHIP (relationship_id ASC);
CREATE INDEX idx_concept_synonym_id ON CONCEPT_SYNONYM (concept_id ASC);
CREATE INDEX idx_concept_ancestor_id_1 ON CONCEPT_ANCESTOR (ancestor_concept_id ASC);
CREATE INDEX idx_concept_ancestor_id_2 ON CONCEPT_ANCESTOR (descendant_concept_id ASC);
CREATE INDEX idx_source_to_concept_map_3 ON SOURCE_TO_CONCEPT_MAP (target_concept_id ASC);
CREATE INDEX idx_source_to_concept_map_1 ON SOURCE_TO_CONCEPT_MAP (source_vocabulary_id ASC);
CREATE INDEX idx_source_to_concept_map_2 ON SOURCE_TO_CONCEPT_MAP (target_vocabulary_id ASC);
CREATE INDEX idx_source_to_concept_map_c ON SOURCE_TO_CONCEPT_MAP (source_code ASC);
CREATE INDEX idx_drug_strength_id_1 ON DRUG_STRENGTH (drug_concept_id ASC);
CREATE INDEX idx_drug_strength_id_2 ON DRUG_STRENGTH (ingredient_concept_id ASC);
--Additional v60 indices
--CREATE CLUSTERED INDEX idx_survey_person_id_1 ON SURVEY_CONDUCT (person_id ASC);
--CREATE CLUSTERED INDEX idx_episode_person_id_1 ON EPISODE (person_id ASC);
--CREATE INDEX idx_episode_concept_id_1 ON EPISODE (episode_concept_id ASC);
--CREATE CLUSTERED INDEX idx_episode_event_id_1 ON EPISODE_EVENT (episode_id ASC);
--CREATE INDEX idx_ee_field_concept_id_1 ON EPISODE_EVENT (event_field_concept_id ASC);