默认情况下，SQL Server是否在表的所有列上创建非聚集索引 [英] Does SQL Server creates Non clustered index on all columns of a table by default

问题描述

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.

这篇关于默认情况下，SQL Server是否在表的所有列上创建非聚集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！