If the column is a foreign key, does it need an index?
Usually you have an index on the FK column. Handcrafted, of course.
However, without updating or deleting on the parent table, you probably won't need this. But, if this is not a bottleneck, then why? SQL has to check the link at some point and the index is pretty much needed then for something larger than a few dozen rows.
We have omitted indexes on some FK columns for a multi-valued table deliberately (inserting 5 million rows per day) to manage data space growth. Not performance. Our only case, otherwise we will always have them, unless 110% is proven otherwise.
source to share
Not. Unlike a primary key, a foreign key does not imply an index. An explicit index on a foreign key is useful because foreign keys are often used in joins, and an index can speed up joins.
You should look at sys.dm_db_index_usage_stats after a full coverage test (or after some lengthy amount of daily use) before understanding which indexes understand which indexes are being ignored by the query optimizer when considering your access paths. These statistics are reset after a server restart, so they are irrelevant if the application has not been running long enough or the tests have not covered every request executed on the data with realistic amounts of data.
source to share
Do not do this. The whole point of an index is that it is joining to another table, so it will almost always account for queries and will always account for queries that join the table it is linked to.
What is the possible reason for keeping the foreign key and dropping the index from it?
source to share
It depends. There are several queries that an index on a foreign key will speed up. Of course the updates take longer because the DBMS has to update the index as well as the table. sometimes it's worth it.
Take a look at the merge merge strategy. If your optimizer uses this strategy, it can do massive joins between the table in question and the parent table with only two indexes. Depending on the collection of tables, this can take a significant portion of the time required for the loop join strategy.
source to share