Page-splitting is a natural occurance. Don't forget that it will occur on the non-clustered indexes also.

The clustered index is a very powerful index. Using it in the manner that you're describing may or may not be the best approach. You'll have to evaluate the queries that your application will be using.

This is a very common configuration. It will move all of the INSERTs to the end of the table (as long as the surrogate-key increases in value - like an INDENTITY column with a positive increment). SQL Server has a special algorithm for handling this situation. When a new page is required, SQL Server will append the new page and being filling it instead of doing a 50/50 page split.

Also remember that SQL Server has a cost-based optimizer. As the amount and distribution of the data changes, you might have to re-evaluate your index configuration.

>Hi All,
> Is it a good idea to have incremental system generated ID as clustered index with fill factor about 90%? I feel with this configuration I won’t have any kind of page split in the database. Is it correct?
>
>Please advise.
>
>Thanks in Advance,
>
>Ravi