repo.IndexReferencedReferencing_HasFullColumnsInReferencing - V
type: V ( view ), modify_date: 2022-01-05 18:06:35
RepoObject_guid: 8590291C-9D61-EB11-84DC-A81E8446D5B0
Columns
PK | Column Name | Data Type | NULL? | ID |
---|---|---|---|---|
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
Indexes
idx_IndexReferencedReferencing_HasFullColumnsInReferencing__1
-
IndexSemanticGroup: no_group
-
[column-source_index_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_IndexReferencedReferencing_HasFullColumnsInReferencing__2
-
IndexSemanticGroup: no_group
-
[column-referenced_RepoObject_guid]; uniqueidentifier
-
[column-referencing_RepoObject_guid]; uniqueidentifier
-
-
PK, Unique, Real: 0, 0, 0
idx_IndexReferencedReferencing_HasFullColumnsInReferencing__3
-
IndexSemanticGroup: no_group
-
[column-RowNumberInReferencing]; bigint
-
-
PK, Unique, Real: 0, 0, 0
Column Details
_
referencing_RepoObject_guid
referencing_RepoObject_guid |
|
|
Referenced Columns
sql_modules_definition
repo.IndexReferencedReferencing_HasFullColumnsInReferencing - V script
/*
HasFullColumnsInReferencing:
Filter repo.IndexReferencedReferencing
all columns, existing in referenced, should also exist in referencing
in other words, the referenced index is completely contained in the referencing object
keep in mind, that a [source_index_guid] can be inherited into several [referenced_index_guid]
if the source object is used several times but target columns are different
Thats why we have [RowNumberInReferencing] and [RowNumberInReferencing_Target]
But [RowNumberInReferencing_Target] is a bit hard to understand. it is the [RowNumberInReferencing] stored before (in earlier runs) into [repo].[Index_virtual]
the same index can be inherited several times into the same referenced object, if a source is used several times
for example
SELECT A_A = A.A, B_A = B.A from source_1 as A LEFT JOIN source_1 as B ON ...
normaly these indexes should have different columns
Issue:
if [repo].[Index_virtual].[referenced_index_guid] is missing, then it could be contained in repo.IndexReferencedReferencing
but [referenced_index_guid] is NULL in this case
How we could / should create a missing but possible [repo].[Index_virtual].[referenced_index_guid]?
=> in usp_index_inheritance
not only insert, but also update of [repo].[Index_virtual].[referenced_index_guid] if it is NULL but it has a source_index here in this view
*/
CREATE View repo.IndexReferencedReferencing_HasFullColumnsInReferencing
As
--
Select
T1.source_index_guid
, T1.referencing_RepoObject_guid
, T1.RowNumberInReferencing_Target
, T1.source_index_type
, T1.referenced_RepoObject_guid
, T1.referenced_index_guid
, T2.RowNumberInReferencing
, T2.ColumnsPerIndex
, T3.referencing_IndexPatternColumnGuid
From
repo.IndexReferencedReferencing As T1
Inner Join
repo.IndexColumn_ReferencedReferencing_HasFullColumnsInReferencing_T As T2
On
T1.source_index_guid = T2.index_guid
And T1.referenced_RepoObject_guid = T2.referenced_RepoObject_guid
And T1.referencing_RepoObject_guid = T2.referencing_RepoObject_guid
Left Join
repo.Index_referencing_IndexPatternColumnGuid As T3
On
T3.source_index_guid = T1.source_index_guid
And T3.referencing_RepoObject_guid = T1.referencing_RepoObject_guid