• I find that this is one of the most mis-understood concepts in SQL Server. The belief is that because you have an auto-incrementing key, you will never have fragmentation in the clustered index. Of course, as you have found out - that is not true.

    Why? Very simple if you take the time to understand what fragmentation is in the first place. Fragmentation is simply out of order pages in the b-tree which are caused by page splits. When you try to add data to a page that does not have enough room, SQL Server performs a page split creating a new page. That new page is now out of order and you have fragmentation.

    Data will get added to a page through an insert or update operation. If you modify a VARCHAR column and add data, this will increase the amount of data on that page. If there is not enough room on the page to add that data, SQL Server will create a new page and move the data.

    So, in short - the answer to your question is that your updates to the data are changing the size of that data causing page splits and fragmenting the index. If that fragmentation is happening too often, you need to change the fillfactor to a lower value to account for the update activity.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs