With SQL if you are adding data to a table and the data will always be inserted as a new row at the end of the data then 100% or 0% (which is 100% btw) is beter with regards to coservation of space.
Many times folks will put a fill factor of 90% on fields such as a identity field. The problem is they have wasted space that will never be reclaimed and page splits will still be as prevalent.
Ex. Say you have and indentity field indexed. Now suppose only 10 recors fit per page and at index rebuild there was 37 records.
Index by page
Page 1
1
2
3
4
5
6
7
8
9
10
Page 2
11
12
13
14
15
16
17
28
19
20
Page 3
21
22
23
24
25
26
27
28
29
30
Page 4
31
32
33
34
35
36
37
Now add 3 records and thus
Page 4
31
32
33
34
35
36
37
38
39
40
Now add two more records
Page 4
31
32
33
34
35
Page 5
36
37
38
39
40
41
42
Now it might be that SQL will create a new page when 41 is actually inserted I haven't looked to see if it is that smart (I think it would be)
But the point is the data in pages 1-3 are never affected again since the data is at the end of the index. If you had used a fill factor of 90 then only 9 records would be in each page to begin with, with 1 extra slot left in the example.
Due to this 1 - 37 records would have taken 5 pages to begin with instead of 4. If say you had had 37000000 records you would have a lot of unused space in the early pages never ging to be reclaimed. So in this case a fill factor of 100% is best.
That is what I am talking about.
Now for PAD_INDEX , this will by default be the same as FILL FACTOR. Basically PAD_INDEX is not the non-leaf pages. It works just like FILL FACTOR, you can however override it with a different value if you choose.
From BOL
"Specifies the space to leave open on each page (node) in the intermediate levels of the index. The PAD_INDEX option is useful only when FILLFACTOR is specified, because PAD_INDEX uses the percentage specified by FILLFACTOR. By default, SQL Server ensures that each index page has enough empty space to accommodate at least one row of the maximum size the index can have, given the set of keys on the intermediate pages. If the percentage specified for FILLFACTOR is not large enough to accommodate one row, SQL Server internally overrides the percentage to allow the minimum. "
"Note The number of rows on an intermediate index page is never less than two, regardless of how low the value of FILLFACTOR."
However I will say this.
Basically these will define the range between highest value and lowest on a specific page. It uses these like a Rolladex to decide which page contains the given data from the index. There will always be enough room for 1 more record after an index rebuild no matter what but I feel most folks can get away with nearer 100% no matter what there fill factor but I am still research true effect based on condition.
Kinda Brain-Dead right now, hope that helps.