- the table will actually get rows inserted into it!
- there is no monotonically increasing index key (like a primary key identity column)
- the author is intimately familiar with the usage pattern of the table and knows how to balance the additional I/O required to read the extra pages with the I/O saved by preventing page splits
Otherwise you're just taking an automatic IO performance hit of n% (where n is 100-(FILLFACTOR)) for no good reason.
Particularly ridiculous is the code that I've found which does something like this:
CREATE TABLE #tempstuff (foo int, bar varchar(20))
INSERT INTO #tempstuff SELECT foo, bar FROM dbo.sometable
CREATE INDEX ix_reallyNotUseful ON #tempstuff (foo, bar) WITH FILLFACTOR = 90
First off, make the index clustered. If this table will not see any more inserts, there's no reason to leave it as a heap and then have to create surrogate keys in order to build the non-clustered index. Might as well physically order it by the index keys and gain the additional performance of the clustered index.
Second, the table is not getting any more inserts after this. Why would you care about leaving extra space on the pages? Set the FILLFACTOR to 100!
This has been a public service rant from your irritable neighbor database engineer.