Choosing a clustered index fill factor
Posted on 2004-11-22
I have a new table that I am going populate with about 5 yrs of data. The clustered index (and only index) will be on a datetime column. All data is is batch loaded during wee-hours when nobody is on the system. The data once populated will be very seldom need to be corrected, and after the initial load of historical data, very rarely will data need to be added in-between days. The table will have up to several thousand rows added for any given day going forward.
Table row length totals 40 bytes. Only about half of the days in a given year have any data at all, and when they do, it averages about 18000 rows. If in the rare case of an update, the most likely scenario is that all of a day's data would be deleted and then reloaded. So we want to optimize for query and for efficiency of storage. My questions are:
1) what should my fill-factor be?
2) can I add my historical data ( by day, year etc) in any order or should I go oldest first or does it matter?
3) should load all my historical data before creating the index and then add it later?
4) once the historical data is loaded... should I drop the index, load a data and then and re-create it.. or just leave it set?
Any suggestions greatly appreciated!