Quick method to optimize your foreign key searching

A lot of people do not realize that creating a foreign key does not also create an index.  This is by design and actually a good thing.  Over indexing a table can actually slow querying as every insert or update causes indices to be recalculated.  Over indexing a table will also slow down select statements from that table as the query optimizer will struggle through evaluating all of the indices to pick the one it thinks is best suited for your current search. 

When working on building targeted foreign key indices to speed up a search, I came up with this code block to auto generate the script for me.

SELECT 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = N''IX_' + FK.TABLE_NAME + '_' + CU.COLUMN_NAME + ''') 
BEGIN 
    CREATE INDEX IX_' + FK.TABLE_NAME + '_' + CU.COLUMN_NAME + '  ON ' + FK.TABLE_NAME + '(' + CU.COLUMN_NAME + ');
END
GO
print N''create IX_' + FK.TABLE_NAME + '_' + CU.COLUMN_NAME + ' done''; 
RAISERROR (N'' --------------------'', 10,1) WITH NOWAIT',
       FK.TABLE_NAME AS K_Table,
       CU.COLUMN_NAME AS FK_Column,
       PK.TABLE_NAME AS PK_Table,
       PT.COLUMN_NAME AS PK_Column,
       C.CONSTRAINT_NAME AS Constraint_Name
FROM   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS C
       INNER JOIN
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS FK
       ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
       INNER JOIN
       INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS PK
       ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
       INNER JOIN
       INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS CU
       ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
       INNER JOIN
       (SELECT i1.TABLE_NAME,
               i2.COLUMN_NAME
        FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS i1
               INNER JOIN
               INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS i2
               ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE  i1.CONSTRAINT_TYPE = 'PRIMARY KEY') AS PT
       ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE  PT.Column_name = 'ID'
       AND PK.Table_Name = '{COMMONLY QUERIED TABLE}'
       AND FK.TABLE_NAME <> PK.TABLE_NAME;  

In this statement, I would replace {COMMONLY QUERIED TABLE} with the table name holding the primary key that was used in queries often. This will generate statements to create an index if one does not exist. You could easily modify it to do a drop and add as well.