sql server：根据需要在外键上创建索引 [英] sql server : create indexes on foreign keys where necessary

问题描述

我有很多表有外键，一些有索引，而其他没有。
所有外键名为 FK_<外键名称> ，索引名为 IX_<外键名称> 。

I have lots of tables with foreign keys and some have an index while others have not. All foreign keys are named FK_<name of the foreign key> with indexes named IX_<name of the foreign key>.

有没有一些好的做法，给定外键的列基数，创建（或不）索引？
这可以脚本化为T-SQL命令吗？

Are there some good practices, given the column cardinality of the foreign key, to create (or not) indexes ? Could this be scripted as T-SQL commands ?

推荐答案

无论是通过T-SQL脚本还是通过Designer创建。你的问题有点模糊，所以我不确定如果你也问，是否可以索引所有的外键。但是，如果是，则应该在查询中经常引用的列上创建索引，并且您可以执行以下操作来提高性能：

It does not matter if they are created via a T-SQL Script or via the Designer. Your question is a little ambiguous, so I am unsure if you are also asking if it is okay to index all of the foreign keys. However, if you are, indexes should be created on columns that are referenced frequently in queries and you can do the following to improve performance:

• 运行数据库调整向导，它将提供改进和建议索引的摘要。

• Run the database tuning wizard which will supply a summary of improvements and recommend indexes.

索引所有外键并运行执行计划查看查询的执行速度是否更快或更慢）。

Index all of the foreign keys and run the execution plan (To see if queries are performing faster or slower).

要通过 T-SQL ：

CREATE INDEX IX_INDEX_NAME
ON Table (FieldName); 

要获取所有外键的列表：

To get a list of all Foreign keys:

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

要生成一个脚本，您可以这样做：

To generate a script that applies indexes across all foreign keys you could do this:

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

这篇关于sql server：根据需要在外键上创建索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！