它们是通过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