Playing with Fill Factor
It is already discussed in the previous chapter, Chapter 9, Implementing Index, that when an index is being created, it stores data in the B-Tree format that has a root page, intermediate level, and leaf level. Leaf level, which is the bottom-most level, contains the actual data in a clustered index. Each data page is 8 KB in size.
When insertion/updation/deletion of data to/from a table happens, it tries to insert in the proper data page according to the record being inserted. For example, we have clustered index on SSN number. We are inserting a new row with the SSN number. SQL Server tries to insert that record in an appropriate data page. Suppose our SSN number starts with "2", then it will find the last page that has SSN number starting with "2". SQL Server will insert our new row in that page only. If your 8 KB page is full or doesn't have enough room to accommodate the new row whose SSN starts with "2", it will split the page, and the data in that page will...
