• The terms FILLFACTOR and PAD_INDEX are very confusing due to the names and some default behavior. From BOL:

    FILLFACTOR specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or rebuild.

    PAD_INDEX = { ON | OFF } Specifies index padding. The default is OFF.

    ON - The percentage of free space that is specified by fillfactor is applied to the intermediate-level pages of the index.

    OFF or fillfactor is not specified -The intermediate-level pages are filled to near capacity, leaving sufficient space for at least one row of the maximum size the index can have, considering the set of keys on the intermediate pages.

    For the clustered index, as the leaf level of the index is the data, FILLFACTOR determines free space for inserts or updates that increase the row size.

    For clustered index on a column with an every increasing value ( identity, NEWSEQUENTIALID or current_timestamp) , since inserts are appended, only free space for updates that increase the row size needs to be specified and free space for intermediate-level index is not needed.

    In this scenario, set FILLFACTOR appropriately and PAD_INDEX = OFF.

    For clustered index on a column without an every increasing value , since inserts are not appended, set FILLFACTOR appropriately and PAD_INDEX = ON

    Use the function sys.dm_db_index_physical_stats to determine the degree of fragmentation.

    SQL = Scarcely Qualifies as a Language