All geek questions in one place

Foreign key index or not in SQL Server 2008

Whether to add an index to a foreign key in SQL Server 2008 or is it processed by default. In many of my tables, I have one FK that points to the user accounts table, and most of them are selected using this WHERE Account_FK = id . Thus, the index can be a quick victory in performance, I hope.

+6
source share
2 answers

Generally, you want all of your JOIN keys to have indexes on them, so yes add an index.

If this is a complex key, be sure to put all the appropriate fields in the list of index keys in the appropriate order.

As far as I know, only once when an index is created automatically in SQL Server is when you add the primary key to the heap (non-indexed table) - the PC is automatically assigned by the Index Cluster; or, as Damian points out below, when you add a UNIQUE to a field or set of fields.

+4
source

An incomprehensible reason for adding an index to FK is when you want to delete a row in the main table (your user account). SQL Server checks each FK relation to see if there are any rows that would prevent deletion, and this check is much faster with the index in the FK column in the child tables.

+3
source

Source: https://habr.com/ru/post/892487/

More articles:


All Articles