Saturday, February 04, 2012

Foreign key doesn't create an Index automatically

There are still people who are believing that Foreign Key does create an index automatically by SQL Server. I think since Primary key by default creates a Clustered Index people are assuming that Foreign keys would also create an Index automatically.

This is a myth and SQL Server does NOT automatically create an index on a foreign key columns.

But one of the best practices for Index tuning is to Index all the columns which are part of a foreign key relationship.

Check out the MSDN documentation for the sub heading Indexing FOREIGN KEY Constraints. The first line says "Creating an index on a foreign key is often useful ....". Microsoft wouldn't be saying this if FK is automatically indexed.

No comments: