SQL Server中的复合聚簇索引 [英] Composite Clustered Index in SQL Server
问题描述
我有一个表，其中IDENTITY列作为主键(传统的ID列).
I have a table with a IDENTITY Column as Primary Key (a classic ID column).
SQL Server自动为该主键创建聚簇索引.
SQL Server create automatically a Clustered Index for that Primary Key.
我的问题是:
- 我可以只有一个包含更多列的CLUSTERED INDEX复合文件吗?
如果是，我该如何删除默认的聚集索引并使用此属性重新创建一个新索引.
If yes, how can I drop the default clustered index and recreate a new one with this attributes.
感谢您的支持
推荐答案
是的，每个表只能有一个聚簇索引-数据是由该索引物理排列的，因此最多只能有一个.
Yes, you can only have a single clustered index per table - the data is physically arranged by that index, so you cannot have more than one.
但是，我不建议使用复合聚簇索引.为什么?因为聚集索引应始终为:
I would however not advise to use a composite clustered index. Why? Because the clustered index should always be:
- 尽可能小-带有4字节的INT是完美的
- 稳定-永不改变，因此您不会在所有索引中看到波澜不惊的更新
- 唯一-否则，SQL Server将必须使用人工的4字节值来唯一化"您的条目
- 最佳将是:不断增加
INT IDENTITY非常适合作为聚集索引-我建议您保持这种方式.
INT IDENTITY is perfect as a clustered index - I would advise you keep it that way.
聚集索引列(或列集)也添加到同一张表的每个非聚集索引的每个条目中，因此，如果将聚集索引设置为20、50字节或更大，则开始浪费大量空间-磁盘和服务器内存中的空间，通常会降低系统性能.
The clustered index column (or set of columns) is also added to each and every entry of each and every nonclustered index on that same table - so if you make your clustered index large, 20, 50 bytes or more, you begin to be wasting a lot of space - on disk and in your server's memory, which generally degrades your system performance.
在这里阅读有关聚簇索引的所有信息以及如何成为聚簇索引的好方法:
Read all about clustered indices and what they should be to be good clustered indices here:
- GUIDs as PRIMARY KEYs and/or the clustering key
- The Clustered Index Debate Continues...
- Ever-increasing clustering key - the Clustered Index Debate..........again!
这篇关于SQL Server中的复合聚簇索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
