I am trying to work out what setting for PAD_INDEX I should use for a clustered index on a IDENTITY column, the table is rarely going to be updated after first insert so I am looking for the setting that will result in the most of the space in the pages being used.

However I have found some documentation on the MSDN that seems to conflict with each other (or at least confuse me enough to ask for clarification).

From: http://msdn.microsoft.com/en-us/library/ms174979.aspx

PAD_INDEX = { ON | OFF } When ON, the percentage of free space specified by FILLFACTOR is applied to the intermediate level pages of the index. When OFF or a FILLFACTOR value is not specified, the intermediate level pages are filled to near capacity leaving enough space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages. The default is OFF.

And from: http://msdn.microsoft.com/en-us/library/ms177459.aspx

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0 which means that the leaf-level pages are filled to capacity.

Given the clause When a FILLFACTOR value is not specified, does that mean that the default FILLFACTOR of 0 is ignored regardless of the setting of PAD_INDEX?

Following on from that does that mean that I need to specify both PAD_INDEX = ON and FILLFACTOR = 100 on my identity clustered index to get maximum page usage?

  • The first one applies to intermediate level index pages only, while the second one is about leaf pages. I can't test it, but from these I'd say that if you set FILLFACTOR to 100, your pages (at all levels) will be filled to capacity, regardless of the setting of PAD_INDEX. Aug 31, 2012 at 8:16

1 Answer 1


PAD_INDEX only applies to non leave level pages. By default those are filled full.

So specifying PAD_INDEX=ON together with FILLFACTOR is only useful when you specify a fillfactor smaller then 100%.

In your case, specifying PAD_INDEX is not necessary, since by default your leave pages are how you want them. Full.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.