PAD_INDEX and FILLFACTOR on clustered Identity index

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?

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.

StackExchange Database Administrators Q#23486, answer score: 2