I'm trying to figure out what an ideal fill factor would be for a non-clusetered index of a column such as EmailAddress. If I have a Person table that is frequently added to, a fill-factor of 0 would result in heavy fragmentation of the index since each new person will have an essentially random value here. In my case, the data is written to and read from frequently, but we have almost no changes or deletions. Are there any guidelines for indexing these types of columns regarding fill factor?

CodePudding user response：

Fill Factor is irrelevant unless you rebuild the index. An index with "random" insertion points will generate page splits and naturally maintain room on pages to accommodate new rows, as split pages end up 50% full.

If you do rebuild such an index (which there's often no reason to do), then consider using a fill factor so you don't remove all the free space on pages, which would lead to a flurry of page splits after rebuild, the end result of which will be similar to (but more expensive than) rebuilding with a fill factor.

Emprically, 60-75 is a reasonable choice.