http://stackoverflow.com/questions/278982/are-foreign-keys-indexed-automatically-in-sql-server
I’ve used SQL script similar to paul_nielsen’s to Create Indexes for Foreign Keys and added “if not exists” condition
DECLARE @SQL VARCHAR(max); SET @SQL = ''
SELECT @SQL = @SQL +
'if not exists (select * from sys.indexes
where id=object_id(''' + TableName +''') and name=''Ix' + ForeignKeyName+''')
CREATE INDEX Ix' + ForeignKeyName
+ ' ON ' + TableName + '(' + ColumnName + ');
'
FROM
…....
--SELECT @SQL
print @SQL
GO
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.