sql server : create indexes on foreign keys where necessary

Question

I have lots of tables with foreign keys and some have an index while others have not. All foreign keys are named FK_<name of the foreign key> with indexes named IX_<name of the foreign key>.

Are there some good practices, given the column cardinality of the foreign key, to create (or not) indexes ? Could this be scripted as T-SQL commands ?

Answer 1

It does not matter if they are created via a T-SQL Script or via the Designer. Your question is a little ambiguous, so I am unsure if you are also asking if it is okay to index all of the foreign keys. However, if you are, indexes should be created on columns that are referenced frequently in queries and you can do the following to improve performance:

• Run the database tuning wizard which will supply a summary of improvements and recommend indexes.

• Index all of the foreign keys and run the execution plan (To see if queries are performing faster or slower).

To create an index via T-SQL:

CREATE INDEX IX_INDEX_NAME ON Table (FieldName);  

To get a list of all Foreign keys:

SELECT f.name AS ForeignKey,   OBJECT_NAME(f.parent_object_id) AS TableName,   COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ColumnName,   OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,   COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferenceColumnName  FROM sys.foreign_keys AS f  INNER JOIN sys.foreign_key_columns AS fc  ON f.OBJECT_ID = fc.constraint_object_id 

To generate a script that applies indexes across all foreign keys you could do this:

SELECT 'CREATE INDEX [IX_' + f.name + '] ON ' + OBJECT_NAME(f.parent_object_id) + '(' + COL_NAME(fc.parent_object_id, fc.parent_column_id) + ')]' FROM sys.foreign_keys AS f  INNER JOIN sys.foreign_key_columns AS fc  ON f.OBJECT_ID = fc.constraint_object_id 

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Answer 2

Great work everyone, very helpful.

Adding an enhancement that includes the table schema. You can also exclude FK names if you prefer (I tend not to add indexes on small tables)

SELECT     *  FROM  (     SELECT TOP 99 PERCENT             f.name AS ForeignKeyName          , s.name                  + '.'                 + OBJECT_NAME(f.parent_object_id)                  + '.'                 + COL_NAME(fc.parent_object_id, fc.parent_column_id)              ParentTable          , referencedSchema.name                 + '.'                 + OBJECT_NAME (f.referenced_object_id)                 + '.'                 + COL_NAME(fc.referenced_object_id, fc.referenced_column_id)             ReferencedTable          , 'CREATE INDEX [IX_' + f.name + ']'                 + ' ON '                      + '[' + referencedSchema.name + ']'                     + '.'                     + '[' + OBJECT_NAME(f.parent_object_id) + ']'                     + '('                          + COL_NAME(fc.parent_object_id, fc.parent_column_id)                      + ')'             CreateIndexSql                FROM          sys.foreign_keys AS f          INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id         inner join sys.schemas s on f.schema_id = s.schema_id          inner join sys.tables referencedTable on f.referenced_object_id = referencedTable.object_id         inner join sys.schemas referencedSchema on referencedTable.schema_id = referencedSchema.schema_id      ORDER BY         2, 3, 1  ) a where a.ParentTable not in (     -- Add any exclusions here so you can forget about them       '' )