question

NYSystemsAnalyst avatar image
NYSystemsAnalyst asked

Index Fill Factor

If the clustered index is on the primary key and it is an IDENTITY column, then by definition, any newly inserted records are automatically put at the end of the index. In this instance, is it necessary to have a fill factor of less than 100% because no records will be inserted in the middle of the index?

If this is a completely incorrect statement, please explain.

sql-serverindexing
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Bob Hovious avatar image Bob Hovious commented ·
Assuming the identity starts at 1, because it doesn't have to, and that you won't come back and fill in the gaps later.
0 Likes 0 ·
KenJ avatar image KenJ commented ·
Also assuming the identity increases and the clustered index is sorted ascending. a descending clustered index with an ascending identity would want to insert every row into the top of the index and vice versa
0 Likes 0 ·
Matt Whitfield avatar image
Matt Whitfield answered

That depends really on what else is in the table. Yes, if you have rows that are then not touched, you will not need a fill factor of less than 100%.

However, if your table has variable length columns (varchar, varbinary, nvarchar), which you then come back to and extend, then you will suddenly be in page split city.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Felipe Ferreira avatar image
Felipe Ferreira answered

no. you are completely correct about this. fill factors below 100% are only useful if you will have a lot of inserts in the middle of the page, that could cause page splits to occur. if your clustered index is in an identity column, this will not happen, so you can use 100% fill factor

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther avatar image Håkan Winther commented ·
I disagree, you'll get page splits when you try to update records with variable length columns.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.