默认情况下，SQL Server是否在表的所有列上创建非聚集索引

本文介绍了默认情况下，SQL Server是否在表的所有列上创建非聚集索引的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

sql server会创建任何默认的非聚集索引吗？我们真的应该将所有FK作为非聚集索引吗？这里的权衡是什么

Will sql server create any default non-clustered index? Should we really put all FK as non-clustered index? What is the trade-off here

推荐答案

不，SQL Server不会自动创建非聚集索引。 除非您的 CREATE TABLE 语句另有说明，否则将根据主键自动创建聚簇索引。

No, SQL Server does not automatically create non-clustered indexes. A clustered index is created automatically based on the primary key, unless your CREATE TABLE statement says otherwise.

是的，我建议索引外键列，因为这些列最有可能使用 IN ， EXISTS 等。但是，请注意，低基数值集（例如性别）的索引相对无用，因为值的差异不足。

Yes, I would recommend indexing foreign key columns because these are the most likely to be JOIN'd/searched against using IN, EXISTS, etc. However, be aware that an index on a low cardinality set of values (gender for example) will be relatively useless because there's not enough difference in the values.

与所有索引的权衡是它们可以加速数据检索，但减慢数据插入/更新/删除速度。还需要执行维护，因为它们可能会碎片化（如硬盘驱动器），但也可能不会随着时间的推移而被使用。索引也占用了驱动器空间。

The trade-off with all indexes is that they can speed up data retrieval, but slow down data insertion/updating/deletion. There's also maintenance that needs to be performed because they can get fragmented (like hard drives) but also might not get used over time. Indexes also take up drive space.