non-indexed foreign keys
Here, you only have to index the parent table, the one that has the primary key
But if you only do that, you have to do a table scan every time you modify the parent or the child
For example:
use adventureworks2022
select
f.name fk_name,
child_tbl.name as child_table_name,
child_col.name as child_column_name,
parent_tbl.name as parent_table_name,
parent_col.name as parent_column_name
from sys.foreign_keys f inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
inner join sys.tables child_tbl on child_tbl.object_id =f.parent_object_id
inner join sys.columns child_col on child_col.column_id =fc.parent_column_id
and child_col.object_id = child_tbl.object_id
inner join sys.tables parent_tbl on parent_tbl.object_id = f.referenced_object_id
inner join sys.columns parent_col on parent_col.column_id = fc.referenced_column_id
and parent_col.object_id = parent_tbl.object_id
order by child_tbl.name,child_col.name;
Now what we did here, same as the docs query, we got the foreign key names from the sys.foreign_keys
Then we wanted the column names, not just the tables so we added the sys.foreign_key_columns
Now we have to filter it by child vs parent, in this DMV terminology parent vs referenced tables so we could get the definition
And with that, we ended it
Now the output was like:
Now if we check through the diagrams, we get:
So the child is the bill of materials table, column is componentID
The parent is Product, the column is ProductID
Same as our query, we are golden.
Now, if we select like:
go
select*
from Production.BillOfMaterials
where ProductAssemblyID = 805 and ComponentID =1
and 1 = (select 1)
We get:
So here we have a primary key nonclustered on the BillOfMaterialID
and a clustered index that has 3 columns, the second key in it is component id, so it is a composite index
or we select like:
go
select*
from Production.BillOfMaterials
where ProductAssemblyID = 805 and ComponentID =1
and 1 = (select 1)
We get:
Now we can check for both by doing the following:
select
i.name as index_name,
i.type_desc as index_type,
i.is_unique,
c.name as column_name,
ic.key_ordinal,
case when ic.is_included_column = 1 then 'yes' else 'no' end as is_included_column
from
sys.tables t
inner join sys.indexes i
on t.object_id = i.object_id
inner join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
inner join sys.columns c
on ic.column_id = c.column_id
and t.object_id = c.object_id
where
t.object_id = object_id(N'production.billofmaterials')
order by
i.name,
ic.key_ordinal,
ic.is_included_column;
Now, index_id is only table wise unique, so you have to include object_id in each join
Otherwise, you will end up with a lot of nasty stuff
The output was like:
So we have one composite and 2 regular nonclustered
Now, if we go to the production.product like:
select
i.name as index_name,
i.type_desc as index_type,
i.is_unique,
c.name as column_name,
ic.key_ordinal,
case when ic.is_included_column = 1 then 'yes' else 'no' end as is_included_column
from
sys.tables t
inner join sys.indexes i
on t.object_id = i.object_id
inner join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
inner join sys.columns c
on ic.column_id = c.column_id
and t.object_id = c.object_id
where
t.object_id = object_id(N'production.product')
order by
i.name,
ic.key_ordinal,
ic.is_included_column;
We get:
Now, since both are identity columns, we can’t update either
But in the case of an update, insert, or delete
You have to go through component_id
And this would require an index scan, since it is a composite
So you have to go create an index for it
Or the other choice is to make it the first key in the clustered
Or you have to deal with the scans each time you query them
Now, how could we check for these insufficiencies:
select
f.is_disabled,
f.is_not_trusted,
f.name as fk_name,
child_s.name + '.' + child_tbl.name as child_table_name,
parent_s.name+'.'+ parent_tbl.name as parent_table_name,
f.update_referential_action_desc,
f.delete_referential_action_desc,
fc.col as fk_columns
from sys.foreign_keys f inner join sys.tables child_tbl on child_tbl.object_id = f.parent_object_id
inner join sys.schemas child_s on child_tbl.schema_id = child_s.schema_id
inner join sys.tables parent_tbl on parent_tbl.object_id = f.referenced_object_id
inner join sys.schemas parent_s on parent_s.schema_id = parent_tbl.schema_id
cross apply(
select (
select
UPPER(c.name) as [text()], ',' as [text()]
from sys.foreign_key_columns fc
inner join sys.columns c
on fc.parent_object_id = c.object_id
and fc.parent_column_id = c.column_id
where fc.constraint_object_id = f.object_id
order by fc.constraint_column_id
for xml path ('')
) as col
)as fc
where
not exists
(
select*
from sys.indexes i
cross apply
(
select
(
select UPPER(c.name) [text()],',' [text()]
from sys.index_columns ic inner join sys.columns c on ic.object_id=c.object_id
and ic.column_id=c.column_id
where i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.is_included_column = 0
order by
ic.partition_ordinal
for xml path ('')
)as cols
)i_cols
where i.object_id = f.parent_object_id
and charindex(fc.col,i_cols.cols) = 1
and i.is_disabled = 0
and i.is_hypothetical = 0
and i.has_filter = 0
and i.type in (1,2)
)
order by parent_table_name;
So the select clause has our table names, what kind of integrity checks do we have, the fk_name, and whether it is disabled or not
Now we joined filtering by sys.tables and schemas to the foreign key catalog view
Then we cross-applied them to get the text of the columns in an XML file with trailing commas
So far, so good
Now we don’t want all the columns, just the non-indexed ones
So we added the not exists clause
The charindex there gets the first key of each and compares them, and If they are the same, then we are not going to see them
And that is about it
The output was like:
Now, as you can see, we have the column we analyzed before
Whether we want to index them or not depends on our workload
And with that, we finish here
See you in the next blog