不管它们是通过 T- SQL 脚本还是通过设计器创建的。 您的问题有点模糊不清, 所以我不确定您是否也在询问是否也可以对所有外国密钥进行索引。 但是, 如果您是, 索引应该放在查询中经常引用的列上, 您可以做以下工作来改进性能 :

• 运行数据库调试向导, 以提供改进摘要并推荐索引 。

• 将所有外国密钥索引并运行执行计划(看查询是否表现更快或较慢)。

通过 T-SQL 创建索引:

CREATE INDEX IX_INDEX_NAME
ON Table (FieldName); 

要获得全部外国密钥列表 :

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

要生成一个可以在所有外国密钥上应用索引的脚本, 您可以做到这一点 :

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" rel="noreferr">http://msdn.microsoft.com/en-us/library/ms188783.aspx

大家干得漂亮,很有帮助

添加包含表格公式的增强功能。如果您愿意,也可以排除 FK 名称(我倾向于不在小表格上添加索引)

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
        
)

依据@toepoke.co.uk 回答和 SQLShack post here ,这里是创建所有FK缺失索引的脚本的综合解决方案。

CREATE TABLE #TempForeignKeys
    (
        TableName VARCHAR(100)
       ,ForeignKeyName VARCHAR(100)
       ,ObjectID INT
    )

--check foreign keys that do not have indexes
INSERT INTO #TempForeignKeys
            SELECT OBJ.name, ForKey.name, ForKey.object_id
            FROM   sys.foreign_keys ForKey
                   INNER JOIN sys.objects OBJ ON OBJ.object_id = ForKey.parent_object_id
            WHERE  OBJ.is_ms_shipped = 0

CREATE TABLE #TempIndexedFK
    (
        ObjectID INT
    )

INSERT INTO #TempIndexedFK
            SELECT FK.ObjectID
            FROM   sys.foreign_key_columns ForKeyCol
                   JOIN sys.index_columns IDXCol ON ForKeyCol.parent_object_id = IDXCol.object_id
                   JOIN #TempForeignKeys FK ON ForKeyCol.constraint_object_id = FK.ObjectID
            WHERE  ForKeyCol.parent_column_id = IDXCol.column_id

--SELECT * FROM #TempForeignKeys WHERE ObjectID NOT IN (SELECT ObjectID FROM #TempIndexedFK)
SELECT   *
FROM     (
             SELECT 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
             WHERE  f.object_id NOT IN (
                                           SELECT ObjectID FROM #TempIndexedFK
                                       )
         ) a
ORDER BY 3

DROP TABLE #TempForeignKeys
DROP TABLE #TempIndexedFK