RE: Create Index Script with Fill Factor Question

SSC Guru

Points: 1003853

March 6, 2018 at 6:28 pm

#1982176

Sergiy - Tuesday, March 6, 2018 5:39 PM
It does not matter at all.
FIll Factor is only taken into consideration when an index is rebuilt (reindexed).
If you only drop and re-generate the index, as you mentioned in your post, the fill factor value would not make any difference.
BTW, since SQL 2005 Fill Factor 0 meant the same as 100.
In one of the later versions Fill Factor 0 is removed completely.

That's why it actually does matter. If you have a 100% Fill Factor on a table (clustered index) or non-clustered index that suffers (like Joe Torre said above) out of order inserts or expansive updates to variable width columns and you intend to make the mistake of defragging the index based only on the percent of fragmentation, you'll enter the wonderful world of blocking due to massive page splits right after you rebuild the index. It also has the nasty effect of bloating your log file.

Heh... of course, if you do like the folks did before me where I work and make denormailized tables with 147 columns with a row width of more than 4 KBytes, then, yeah... there's nothing Fill Factor is going do to save you.

Agreed on the 0 Fill Factor. I look at it and 100 as a marker for a probable mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)