I am trying to understand the reason behind index fragmentation.
Assuming fill factor is 100% and the clustered index is on an integer column. The values on each page are as follows and assuming that page can fit only 5 rows.
Page1: 1, 2, 3, 4, 5
Page2: 6, 7, 8, 10, 11
Page3: 12, 13, 14
Question 1: Now when there is an insert of value 9, then 9 added either to page2 (page split) or to a new page?
Question 2: How does this result in fragmentation? Is it because of empty spaces in the page due to page splits?
Question 3: Does clustered index always store data physically ordered in sequential pages or is it ordered logically and does this have any contribution to fragmentation?