repo.IndexColumn_virtual_referenced_setpoint - V

type: V ( view ), modify_date: 2021-08-17 19:46:16

RepoObject_guid: 7D90291C-9D61-EB11-84DC-A81E8446D5B0

Description

Examples

Entity Diagram

entity-repo.indexcolumn_virtual_referenced_setpoint

Columns

Table 1. Columns of repo.IndexColumn_virtual_referenced_setpoint - V
PK Column Name Data Type NULL? ID

1

uniqueidentifier

NOT NULL

2

int

NOT NULL

bit

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

uniqueidentifier

NULL

int

NULL

Foreign Key Diagram

entity_1_1_fk-repo.indexcolumn_virtual_referenced_setpoint

References

Object Reference Diagram - 1 1

entity_1_1_objectref-repo.indexcolumn_virtual_referenced_setpoint

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-repo.indexcolumn_virtual_referenced_setpoint

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-repo.indexcolumn_virtual_referenced_setpoint

Column Reference Diagram

entity_1_1_colref-repo.indexcolumn_virtual_referenced_setpoint

Indexes

PK_IndexColumn_virtual_referenced_setpoint

idx_IndexColumn_virtual_referenced_setpoint__2

idx_IndexColumn_virtual_referenced_setpoint__3

Column Details

_

index_guid

1

index_guid

uniqueidentifier

NOT NULL

Referenced Columns

is_descending_key

is_descending_key

bit

NULL

referenced_index_guid

referenced_index_guid

uniqueidentifier

NULL

referenced_RepoObject_guid

referenced_RepoObject_guid

uniqueidentifier

NULL

referenced_RepoObjectColumn_guid

referenced_RepoObjectColumn_guid

uniqueidentifier

NULL

referencing_RepoObject_guid

referencing_RepoObject_guid

uniqueidentifier

NULL

referencing_RepoObjectColumn_guid

referencing_RepoObjectColumn_guid

uniqueidentifier

NULL

RowNumberInReferencing

RowNumberInReferencing

int

NULL

sql_modules_definition

repo.IndexColumn_virtual_referenced_setpoint - V script
/*
Sollwert
jeder Index in [repo].[Index__virtual], der einen [referenced_index_guid] hat, der also geerbt wird
benötigt für diesen geerbten Index auch geerbte Spalten
alle benötigten Spalten werden hier aufgelistet

Setpoint:
each index in [repo].[Index__virtual], having a [referenced_index_guid] (the index is inherited)
also needs the columns
these required columns are listed here
this view is a "setpoint"
all these columns should exists in [repo].[IndexColumn__virtual]



isue:
some combinations of ([index_guid], [index_column_id]) are not unique
why?
because they have multiple [referencing_RepoObjectColumn_guid]
but this is wrong, only one referencing_RepoObjectColumn_guid is possible per [index_column_id]

=> [RowNumberInReferencing]



*/
CREATE View repo.IndexColumn_virtual_referenced_setpoint
As
--
Select
    i.index_guid                         --referencing index, which inherits columns from referenced index
  , ic.index_column_id
  , ic.is_descending_key
  , ic.referencing_RepoObjectColumn_guid --columns in the referencing RepoObject
  , i.referenced_index_guid              --referenced index, which is the source for inherited columns
  , i.RowNumberInReferencing
                                         --only for testing:
  , ic.referenced_RepoObjectColumn_guid
  , ic.referenced_RepoObject_guid
  , ic.referencing_RepoObject_guid
From
    repo.Index_virtual                                                       As i
    Inner Join
        repo.IndexColumn_ReferencedReferencing_HasFullColumnsInReferencing_T As ic --setpoint belongs to the parent_RepoObject of the index
            On
            ic.referencing_RepoObject_guid = i.parent_RepoObject_guid
            --ic.index_guid is the referenced index (source index)
            And ic.index_guid              = i.referenced_index_guid
            And ic.RowNumberInReferencing  = i.RowNumberInReferencing