非唯一的集群键是否会增加页面级锁定的可能性？
我有一个包含许多列的表，其中总最大大小大大超过 8k 边界。该表包含一个 ModuleID 列，它基本上告诉您它是什么类型的对象（不用担心 - 我没有设计这个），其中可能有 15 个不同的值。然后它有一个名为 propertyID 的唯一列，它也是一个 IDENTITY(1,1)，然后由 SQL Server 递增。 ModuleID 上有一个聚集索引，该值对于选择始终是已知的，对于更新使用 propertyID（moduleID 很少在此处的范围内）。表包含数百万行。
关于 INSERT 我的问题是：
a) 非唯一聚集键是否会增加 SQL Server 持有独占页级锁而不是 KEY（行）锁的可能性？
b) 将聚集键更改为递增的唯一 propertyID 将使 SQL Server 能够持有独占 KEY 锁，并且这些锁将始终进入聚集索引的最后一页？
该表（在某些安装中）根据 moduleID 进行分区的事实会改变您的答案吗？
I have a table with many columns of which the total max size greatly exceeds the 8k boundary. This table contains a ModuleID column which basically tells u what type of object it is (dont worry - I didnt design this) of which there are maybe 15 different values. And then it has a unique column called propertyID which is also an IDENTITY(1,1) and then incremented by SQL Server. There is a clustered index on ModuleID, this value is always known for selects, for updates propertyID is used (moduleID is rarely in scope here). Table contains several million rows.
Regarding an INSERT my questions are thus:
a) Does the non-unique clustered key increase the likelyhood of SQL Server holding exclusive page-level locks instead of KEY (row) locks?
b) Changing the clustered key to the unique propertyID which is incremented would enable SQL Server to hold exclusive KEY locks instead, and these would always go into the last page of the clustered index?
Would the fact that the table is (in some installations) partitioned on moduleID change your answers?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我没有任何确凿的事实或数字来支持这一点，但根据我的经验和知识，我会说：不。
惊讶？？
我解释一下：SQL Server中的聚簇索引必须是唯一的。是的，必须如此。不，您不必使其唯一 - 如果您不这样做，SQL Server 本身将通过向聚集索引中冲突的行添加 4 字节“唯一符”（INT）来解决这一问题。
所以最后，你总是有一个唯一的聚集键 - 你必须这样做，因为这实际上是指向实际数据的“物理”指针 - 非聚集索引如何能够找到如果不是某种独特的“指针”，那么数据会快速有效地传输吗？
因此，即使您有点粗心，为集群键选择的一列（或一组列）不能保证是唯一的，SQL Server 最终也会确保它们是唯一的 - 可能会付出额外的代价这些唯一符所需的存储空间。
因此，您实际上总是留下一个唯一的集群键，因此，页面级锁定的可能性不应该有任何差异。
I don't have any hard fact or numbers to back this up, but from my experience and knowledge, I'd say: no.
Surprised??
Let me explain: the clustering index in SQL Server must be unique. Yes, it must. No, you don't have to make it unique - if you don't, SQL Server itself will take care of that by adding a 4-byte "uniqueifier" (an INT) to your rows that clash in the clustering index.
So in the end, you always have a unique clustering key - you have to, since this is really the "physical" pointer to the actual data - how else would a non-clustered index be able to find that data quickly and efficiently, if not for a unique "pointer" of sorts??
So even if you're a bit careless and chose a column (or set of columns) for your clustering key that aren't guaranteed to be unique, SQL Server will make sure they are, in the end - possibly at the expense of additional storage needed for those uniqueifiers.
So you're really always left with a unique clustering key, and thus, there shouldn't be any difference in the likelihood of page level locks.