修改任何行数据会使我的聚集索引碎片化吗？
我现在明白聚集索引包含所有行数据，而不仅仅是索引字段。我试图理解这对于碎片化的影响。
假设我们有一个这样的表：
create table Files
(
ID uniqueidentifier not null,
Field1 nvarchar(300) null,
Field2 nvarchar(300) null,
Field3 nvarchar(300) null,
Binary varbinary(max) null
)
现在假设所有这些行都充满了数据，然后在聚集索引中的一些较早的行上，您突然将 Field1、Field2、Field3 和 Binary 设置为 null。
正如我以一种相当幼稚的方式思考的那样，其中的一个含义是，清除所有这些值将产生间隙，并且索引将变得支离破碎。我猜行的顺序仍然正确，那么这真的是索引碎片吗？
或者你也可以换个角度思考；如果它们一开始都是空的并且您插入数据，那么您最终是否必须将数据转移到不同的页面并获得索引碎片？
此外，我知道 LOB 数据存储在单独的分配单元中，尽管我不确定这意味着什么；这是否意味着将 Binary 设置为 null（或填充它）对聚集索引碎片没有影响？
I now understand that a clustered index contains all of the row data, not just the index fields. I'm trying to understand the implications of this in regards to fragmentation.
Say we have a table like this:
create table Files
(
ID uniqueidentifier not null,
Field1 nvarchar(300) null,
Field2 nvarchar(300) null,
Field3 nvarchar(300) null,
Binary varbinary(max) null
)
Now lets say that all of these rows are full of data, and then on some of the earlier rows in the clustered index you suddenly set Field1, Field2, Field3 and Binary to null.
One implication of this, as I think of it in my rather naive way, is that clearing all of these values will create gaps, and the index would become fragmented. I guess the rows are still in the right order though, so is that really index fragmentation?
Or you can think about it the other way; if they're all null to start with and you insert data, do you end up having to shuffle data out to different pages and also get index fragmentation?
In addition, I know that LOB data is stored in a separate allocation unit, though I'm not sure what the implications of that are; does it mean that setting Binary to null (or populating it) should have no effect on clustered index fragmentation?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
是的。你会得到内部碎片。 SQL Server 不会自动压缩数据页来回收空间。您可以使用 SQL Internals Viewer 工具来查看这一点。尽管取决于您的工作量，但这不一定是坏事。一定程度的内部碎片有助于缓解以下问题（甚至可以通过使用 FillFactor 故意添加）
是的。假设页面上没有足够的空白空间来容纳较长的行，您将进行页面拆分以为新数据腾出空间。逻辑顺序将变得与物理顺序不同，并且您会得到外部碎片。
Yes. You will get internal fragmentation. SQL Server won't automatically compact the data page to reclaim the space. You can use the SQL Internals Viewer tool to see this. This isn't necessarily a bad thing though dependant on your workload. Some degree of internal fragmentation can be useful to mitigate the below (and even added deliberately through use of FillFactor)
Yes. Assuming that there is not sufficient empty space on the page to accommodate the longer row you will get page splits to make room for the new data. The logical order will become different than the physical order and you get external fragmentation.