聚簇索引必须是唯一的吗？ [英] Do clustered indexes have to be unique?

问题描述

如果聚簇索引不是唯一的，会发生什么？是否会导致错误的性能，因为插入的行流到某些类型的溢出页面？

它是made是唯一的，如果是如此？

我问的是因为我目前使用聚集索引来划分逻辑部分中的表，但是性能是这样的，所以，最近我得到了通知使我的聚集索引唯一。

解决方案

p>他们不是是唯一的，但它一定是鼓励。

我没有遇到一个场景，我想在一个非唯一的CI创建一个

如果您在非唯一列上创建CI

如果聚簇索引不是唯一的
index，SQL Server通过在内部添加一个内部
生成的值来使任何重复的
键是唯一的

这会导致不良的效果吗？

添加唯一性标识符肯定会在计算和存储它时增加一些开销。

如果此开销将显着取决于几个因素。

• 表包含多少数据。 >
  
• 插入的速率是多少。


• 在选择中使用CI的频率（当没有覆盖索引存在时，几乎总是如此）。

编辑

由Remus在评论中指出，创建非唯一的CI将是一个合理的选择。我没有遇到一个这样的情况只是表明我自己缺乏曝光或能力（选择你的选择）。

What happens if a clustered index is not unique? Can it lead to bad performance because inserted rows flow to an "overflow" page of some sorts?

Is it "made" unique and if so how? What is the best way to make it unique?

I am asking because I am currently using a clustered index to divide my table in logical parts, but the performance is so-so, and recently I got the advice to make my clustered indexes unique. I'd like a second opinion on that.

Thanks!

解决方案

They don't have to be unique but it certainly is encouraged.
I haven't encountered a scenario yet where I wanted to create a CI on a non-unique column.

What happens if you create a CI on a non-unique column

If the clustered index is not a unique index, SQL Server makes any duplicate keys unique by adding an internally generated value called a uniqueifier

Does this lead to bad performance?

Adding a uniqueifier certainly adds some overhead in calculating and in storing it.
If this overhead will be noticable depends on several factors.

• How much data the table contains.
• What is the rate of inserts.
• How often is the CI used in a select (when no covering indexes exist, pretty much always).

Edit
as been pointed out by Remus in comments, there do exist use cases where creating a non-unique CI would be a reasonable choice. Me not having encountered one off those scenarios merely shows my own lack of exposure or competence (pick your choice).

这篇关于聚簇索引必须是唯一的吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！