Will modifying any row data fragment my clustered index?

Question

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?

Answer 1

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?

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)

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?

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.