Solved Indexing a foreign key in the child table

[nector]

I'm not a fan of indexing the foreign in my SQL Server Database, some people suggest that indexing the foreign can help queries data retrieval faster as but the queries be parameterized. Especially when the front end is MS Access, could this be true? If yes any supporting material evidence?

 

[Josef P.]

I'm not a fan of indexing the foreign in my SQL Server Database

What is the reason for this?

An index can speed up the filtering of data records. Whether this goes to a foreign key data field is irrelevant.

Example:

select
     P.idParent, ... , C.SomeFields
from
     ParentTable P
     left join ChildTable C ON C.fkParent = P.idParent
where
    P.SomeField = 'xyz'

Here an index for ParentTable.SomeField helps to filter the data records of ParentTable and an index for ChildTable.fkParent helps with the join.

select
     P.idParent, ... , C.SomeFields
from
     ChildTable C
     left join
     ParentTable P ON P.idParent = C.fkParent
where
    C.DetailChildField = 123

An index for ChildTable.fkParent will not bring any acceleration.

Note:
Since you are using the SQL server, you can take a good look at the execution plans. In particular, check the queried data records and also check logical reads and physical reads (with statistics io).

 

[ebs17]

I'm not a fan

Where would you use an index?

An index can help with all comparative operations:
JOIN (key of one table against key of the other table),
Filter (key against comparison value),
Group (key value against key value in the same table),
Sort (key value against key value in the same table)
partial aggregation (min, max)

The SQL optimizer uses its own algorithms to determine whether an existing index is actually used.

 

[nector]

I want to filter related record quickly and below is the suggestion I'm getting Microsoft:

Indexes on Foreign Key Constraints​

Unlike primary key constraints, creating a foreign key constraint doesn't automatically create a corresponding index. However, manually creating an index on a foreign key is often useful for the following reasons:

• Foreign key columns are frequently used in join criteria when the data from related tables is combined in queries by matching the column or columns in the foreign key constraint of one table with the primary or unique key column or columns in the other table. An index enables the Database Engine to quickly find related data in the foreign key table. However, creating this index isn't required. Data from two related tables can be combined even if no primary key or foreign key constraints are defined between the tables, but a foreign key relationship between two tables indicates that the two tables have been optimized to be combined in a query that uses the keys as its criteria.

Primary and Foreign Key Constraints - SQL Server | Microsoft Learn

 

[nector]

EBS 17 is right here, thanks

 

[Pat Hartman]

That help entry is gobbledy gook. It doesn't make any sense. You also don't say whether it is Jet/ACE or SQL Server because Access always creates a HIDDEN index for every foreign key. When you upsize your Jet/ACE tables to SQL Server - SSMA does NOT transfer these indexes so be ware.

If you create your own index on a FK in your Access application, you now have TWO identical indexes on the same field which surely can't be good.

There is always a tradeoff between too many and too few indexes. I let Access index my FKs, not that I can stop it, and then recreate them manually when I upsize.

 

[ebs17]

You also don't say whether it is Jet/ACE or SQL Server

in my SQL Server Database

Find the contradiction.

 

[Pat Hartman]

Find the contradiction.

I'm trying to figure out what you are arguing with me about. I don't see that I disputed your position.

 

[kentgorrell]

Re: Access always creates a HIDDEN index for every foreign key.
If you create your own index on a FK in your Access application, you now have TWO identical indexes on the same field which surely can't be good.

What?
Why wasn't I told?

so I just iterated the index collection of a table and looked at the fields and you are correct!

Thanks for this enlightenment, I'll stop adding extra indexes for FKs in Access dbs.
apparently the Access team decided that indexes on FKs are essential and I would agree given how often one would filter or join on a FK in queries. And then of course, sub form Master / Slave (or is that child?)

 

[Pat Hartman]

Here's the bad thing I just discovered. SSMA does NOT upsize these hidden indexes!!!!!!!!!!!!!! So, I'm going through the ACE database and adding them by hand. Grrrrrrrrrrrrrrrrrrrr.

 

[ebs17]

I'll stop adding extra indexes for FKs in Access dbs

The index for the foreign key is only set automatically in an Access file if referential integrity is set in the relationship. But again, this should be standard.

 

[Minty]

Here's the bad thing I just discovered. SSMA does NOT upsize these hidden indexes!!!!!!!!!!!!!! So, I'm going through the ACE database and adding them by hand. Grrrrrrrrrrrrrrrrrrrr.

@Pat Hartman I believe you are correct, however it does create the table level relational constraints, if you have set up relationships in Access.

 

[Pat Hartman]

Yes it creates the constraints but it should also upsize the hidden indexes or Access shouldn't create them. The teams don't appear to talk to each other.

 

[kentgorrell]

Yes it creates the constraints but it should also upsize the hidden indexes or Access shouldn't create them. The teams don't appear to talk to each other.

Yes it should but then there are lots of things that the SSMA does now that it once didn't.

FYI: while following your advice to remove explicit indexes where an implicit index already exists for a FK, I found one interesting anomoly. When trying to delete one index (in table design view) I got the following message
Application can't delete this index
This table participates in one of more relationships
Delete its relationship in the Relationships window first

The relationship in question was one to one. Not sure if that's why.
I deleted the relationship, then the index and re-established the relationship.
This only happened for one out of a dozen or so.

 

[kentgorrell]

The index for the foreign key is only set automatically in an Access file if referential integrity is set in the relationship. But again, this should be standard.

I'm trying to think of when you might want a relationship but not use RI. I guess maybe to improve join performance while ignoring orphans?

 

[Pat Hartman]

I'm trying to think of when you might want a relationship but not use RI. I guess maybe to improve join performance while ignoring orphans?

No reason I know of.

 

[ebs17]

No, a relationship without an RI set is practically useless and should only be started if necessary or out of ignorance.
The only benefit: In the database schema there is a visual indication that two tables logically belong together.
Possible need: According to Access specifications, indexes and connections to other tables in queries are limited in number. If you attach a lot of lookup tables to a table, you could end up in an emergency situation like this.

 

[kentgorrell]

@Pat Hartman I believe you are correct, however it does create the table level relational constraints, if you have set up relationships in Access.

No, a relationship without an RI set is practically useless and should only be started if necessary or out of ignorance.
The only benefit: In the database schema there is a visual indication that two tables logically belong together.
Possible need: According to Access specifications, indexes and connections to other tables in queries are limited in number. If you attach a lot of lookup tables to a table, you could end up in an emergency situation like this.

and by "connections" you mean relationships?
I really only work with SQL Server dbs so I haven't used Lookups since Access Web Apps. I'd kinda forgotten about them. Do they create some kind of relationship in the background? (For Access tables. Obviously not applicable to SS) or are they visible in the relationship window.

 

[ebs17]

In queries with multiple tables, relationships are often modeled as joins, but other than that, relationships can be linked in completely different ways depending on the goal of the query. So it's all about the sum of relationships and connections.

I was talking about lookup tables.
If you have a people table, you quickly have lookup tables for salutation, title, gender, etc. often small things in terms of the content contained and in terms of the real need for an index. This is not unusual.
In principle, any table with a large number of foreign keys should be considered.

 

[kentgorrell]

Yes it creates the constraints but it should also upsize the hidden indexes or Access shouldn't create them. The teams don't appear to talk to each other.

I was just thinking about all the things to consider when migrating.
For example, An Access index has a boolean option for Ignore Null while in SS you need to apply a filter to the index (I think SSMA now does that) but you should only do that if the column allows nulls.

FKs in SS have options for all the Access options, Enforce, Cascade Delete, Cascade Update, Cascade to Null but also one that Access doesn't have: Cascade to Default. Useful for when, for example, you have a default on the Customer ID column in your Invoice table that has the Customer ID of the Cash Customer so when you decide to delete all your customers who haven't made a purchase in the last year or so then deleting the customers changes the Customer ID to Cash. Nice.

And there are some significant differences that should be addressed propertly, IMO, rather than just letting SSMA do its thing.

So I find that doing a review of the new structure, creating tables and columns and creating constraints like DFs, FKs and Indexes with nice names (and the FK and Index can have the same name) before migrating the data is a good thing to do. PKs are a bit weird. Unlike FKs, Access creates Indexes that are visible but with 'PrimaryKey' as the name. SS creates an Index with the same name as the PK constraint but this name must be unique to the db not just the table. SSMA deals with that... badly. SS does have SPs to change PK names after the event but still.

And yes I know that SSMA breaks these two processes up into separate tasks but I find this a good time to do a full review on the structure, indexes and constraints and their naming rather than migrating problems and crappy names. And it's a good time to fix bad data rather than just bung it in with No Checks.