From what I've read, typically for indexing the recommendation is to leave the fill factor
at 70-80, and the logic behind this seems that new data may be inserted into a table between existing values thus fill factor
shouldn't be set as 100. In a hypothetical example of usernames, new names may be added to the name field, so a fill factor of 100 could create a problem because, even though the pages are full, the new data won't be organized appropriately with the other data.
My question involves a table where data are organized by date (clustered index) and ID (non clustered index); each order has a date and an ID thus no "new" orders can occur between existing orders and no new IDs can appear between existing IDs - these happen sequentially. For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100, meaning are there performance advantages to having the pages full since no new data will appear between existing data?
For a table like this, would it be appropriate from a performance perspective to set the fill factor to 100 and reindex at 100
-- for those two indexes, yes.