I understand fill-factor, pages and index structure and I therefor understand why a 100% fill factor is a rare best practice. So why does it default to 0 (or 100% ) by default? Why not 90 or 95?
Is there something I'm missing?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Just throwing out a few suggestions here as to why Microsoft would do this:
INSERTed data will most likely be at the end of the table, making the extra space per page useless
UPDATEd data will not lengthen row data very often, causing a page split
This is just guessing here. The only people that can really answer that question accurately is the SQL Server team themselves.