I had an interesting conversation with a colleague today who insisted that when you create a foreign key in Microsoft SQL Server, it automatically adds an index on the foreign key.  Guess what — it doesn’t!!  I think this is one of the most common mistakes made in SQL Server.  I’ve seen wonderfully normalized databases where the developer did not create any indexes on the foreign keys.  With today’s fast hardware speeds, this problem is often masked because when you’re working with a new database, everything usually runs smoothly anyway.  It is after time that the problem manifests itself, as that database grinds down under the pressure of doing table scans in order to achieve joins.

So I did some searching online and found a great script on Paul Nielson’s blog that will generate missing foreign key indexes for you.  Thanks Paul!

Now my recommendation would be to run this script, but hold off on actually executing it until you review it, make sure you haven’t already created indexes, double-check everything, etc.  Remember that you can DESCREASE performance sometimes with too many indexes.  But usually, this will improve your performance

Here is Paul’s script:

/* This script creates a (composite) nonclustered index
  for every Foreign Key without a complete matching (composite) index 

FK Index script
Paul Nielsen
www.sqlserverbible.com

----------------------------------
version 1.00 - Feb 8, 2007

*/

CREATE
-- alter
Function FKCol (@Object_ID INT)
RETURNS VARCHAR(8000)
as
Begin
  DECLARE @SQL VARCHAR(max)
  SET @SQL = ''
  select @SQL = @SQL + pc.name + ','
    from sys.foreign_key_columns fk
      join sys.columns pc
        on fk.parent_object_id = pc.object_id
          and fk.parent_column_id = pc.column_id
    where fk.constraint_object_id = @Object_ID;
set @SQL = left(@SQL, len(@SQL)-1);
Return @SQL
End

-- Dynamic SQL
DECLARE @SQL VARCHAR(max); SET @SQL = ''
SELECT @SQL = @SQL + ' CREATE INDEX Ix' + FK_Name
     + ' ON ' + FK_Table + '(' + FK_Columns + ');'
  FROM (
    -- FK w/o complete (composite) index
        select distinct fko.name as FK_Name, fks.name + '.'
             + fkt.name as FK_table, dbo.FKCol(fk.constraint_object_id) as FK_Columns
          from sys.foreign_key_columns fk
                join sys.objects fkt
                  on fk.parent_object_id = fkt.object_id
                join sys.schemas as fks
                  on fks.schema_id = fkt.schema_id
                join sys.objects fko
                  on fk.constraint_object_id = fko.object_id
                left join sys.index_columns ic
                  on ic.object_id = fk.parent_object_id  -- same table
                        and ic.column_id = fk.parent_column_id  -- same column
                        and ic.index_column_id = fk.constraint_column_id
                              -- column position in the index and FK
          where ic.object_id IS NULL) sq
SELECT @SQL
EXEC (@SQL) 

-- check for updates at www.SQLServerBible.com