SQL Script to create indexes for Foreign keys


I’ve used SQL script similar to paul_nielsen’s to Create Indexes for Foreign Keys and added “if not exists” condition

‘if not exists (select * from sys.indexes
 where id=object_id(”’ + TableName +”’) and name=”Ix’ + ForeignKeyName+”’)
 CREATE INDEX Ix’ + ForeignKeyName
    + ‘ ON ‘ + TableName + ‘(‘ + ColumnName + ‘);

print @SQL

At the end I would recommend to print @SQL to show all new lines correctl in messages ta of SSMS,
rather than SELECT @SQL or execute SQL


Another script can be found here(login is required): http://www.sqlservercentral.com/scripts/Index+Management/62069/

From http://msdn.microsoft.com/en-us/library/ms175464.aspx
Indexing FOREIGN KEY Constrains

Creating an index on a foreign key is often useful for the following reasons:

  • Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables.
  • 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.