>> Suppose there is a table with a compound primary key and said key is clustered.
>>When I insert a record, I'm taking an educated guess that because of the clustered index, the actual table is re-ordered in some manner if a new record falls between other records.
>>
>>And yes, that's generally correct. Now, you can set the FILL FACTOR to reduce how frequently page splits occur. If you set a value of 80 (and this is from MSDN), that means 20% of the data page is left empty, to allow/reserve for space that would otherwise cause a page split. I've read different reports on how effective this can be. So mileage will definitely vary based on the usage.
>
>But that means that FILL FACTOR is a workaround.

Agreed - though in a highly insert-intensive environment where a special clustered index is needed, it can be helpful. In these types of situations, a DBA is trying to balance fragmentation, insert performance, etc.


Not to get too far off-topic, but something MS has added in SQL 2014 is a game changer for high insert environments They have a new in-memory OLTP engine called Hekaton that blows the doors off the regular OLTP engine for high-insert scenarios. It doesn't benefit everyone but if you're going to be doing anything with SQL 2014, it helps to at least know what Hekaton can do.