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 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); pan style="font-size: 12pt; color: gray; font-family: 'Times New Roman','serif'; mso-fareast-font-family: 'Times New Roman'">

Return @SQL

End

GO

– 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 = tyle="font-size: 14pt; color: black; font-family: 'Tahoma','sans-serif'; mso-fareast-font-family: 'Times New Roman'; mso-no-proof: yes"> fk.constraint_column_id

                              – column position in the index and FK

          where ic.object_id IS NULL) sq

SELECT @SQL

EXEC (@SQL)  – Might want to comment this out until you can review the script

 

– check for updates at www.SQLServerBible.com