I have question on SQL server Foreign Key

Question

Hi,

I am doing some performance improvement process in my sql database. By investigating related with Foreign Key, I am saw from several site - even adding Foreign Key in a table will get performance improvement while joining Primary and Foreign key tables. (There is contradictory message I saw that I will not improve any performance improvement, even using foreign Key columns in the join). I even check using some example in a database. I also not seeing any improvement while using Foreign key in join.

Can you please help me understand are there any performance improvement while using Foreign Key.

Answer 1

Hi Sajith A K •,

Welcome to Microsoft Q&A forum.

As I understand, you are looking some performance improvement in your SQL DB.

Could you please let us know what is the improvement you need, bottlenecks in existing query etc?

A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables to control the data that can be stored in the foreign key table. In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.

For example, the Sales.SalesOrderHeader table has a foreign key link to the Sales.SalesPerson table because there's a logical relationship between sales orders and salespeople. The SalesPersonID column in the SalesOrderHeader table matches the primary key column of the SalesPerson table. The SalesPersonID column in the SalesOrderHeader table is the foreign key to the SalesPerson table. By creating this foreign key relationship, a value for SalesPersonID can't be inserted into the SalesOrderHeader table if it doesn't already exist in the SalesPerson table.

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.

Reference: https://learn.microsoft.com/en-us/sql/relational-databases/tables/primary-and-foreign-key-constraints?view=sql-server-ver16

Also refer to this thread over Stackoverflow for more information: https://stackoverflow.com/questions/507179/does-foreign-key-improve-query-performance#:~:text=Foreign%20Keys%20are%20a%20referential,to%20improve%20look%20up%20times.&text=Good%20models%20(generally)%20perform%20better.

Hope this helps.

Thanks

Answer 2

Hello I went through the stack overflow link. It is useful. So now I understood by adding only foreign key will not increase the performance. I suppose to add only FK. In our server most of the DBs CPU utilization is reaching 100%( Saw from azure portal). So I am investigating each part in DB. I checked the long running queries(most of the queries are Entity Frameworkqueries). Added some indexes, ran rebuild index query. Identifying unwanted indexes. Suggesting to rewrite EF queries.