David Alcock shows a performance benefit from having a foreign key constraint in place:
Foreign keys are used in database design to enforce referential integrity but they also have some performance benefits as well that you might not necessarily notice unless you’re looking into your execution plans.
Let’s take the following query using the AdventureWorks2019 sample database where I’m selecting the BusinessEntityID and JobTitle from the HumanResources.Employee table and by using an inner join I’m only returning rows that have matching values (BusinessEntityID) in both tables:
There are specific rules to table elimination but if you meet the criteria, it can save you a bit of CPU time and I/O.