Menu
What are the advantages, if any exist, of having an an index for every foreign key in a SQL Server database?
207
It is highly recommended to create an index on each foreign key constraint on the child table, as it is very common when calling that key on your queries to join between the child table and the parent table columns, providing better joining performance.
SQL Server will not automatically create an index on a foreign key. Also from MSDN: A FOREIGN KEY constraint does not have to be linked only to a PRIMARY KEY constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.
Since each foreign key value must exactly match the corresponding primary key value, the foreign key must contain the same number and data type of columns as the primary key, and these key columns must be in the same order. A foreign key can also have different column names than the primary key.
Practically, the foreign key has nothing to do with the primary key tag of another table, if it points to a unique column (not necessarily a primary key) of another table then too, it would be a foreign key.
Yes it's a good practice, see here: When did SQL Server stop putting indexes on Foreign Key columns? scroll down to the Are there any benefits to indexing foreign key columns? section
142
Every foreign key? No. Where the selectivity is low (i.e. many values are duplicated), an index may be more costly than a table scan. Also, in a high activity environment (much more insert/update/delete activity than querying) the cost of maintaining the indexes may affect the overall performance of the system.
34
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
