i always keep identity field as my clustered index just to make sure every new record goes at the the end of the page without causing page splits, also it is unique narrow ever increasing few attributes recomanded for clustered index

i always keep identity field as my clustered index just to make sure every new record goes at the the end of the page without causing page splits, also it is unique narrow ever increasing few attributes recomanded for clustered index

An ever-increasing clustering key has the advantage that any new rows that are added will always be added at the "end". That will avoid page splits if you are mostly inserting into the table. Compare that with a clustering key that is a GUID. With a GUID, when you add a new row, SQL Server may be required to insert it into some page where that GUID happens to fall in the ordering scheme. If that page does not have enough space to hold the new data, then a new page will need to be inserted (page split). An ever-increasing key avoids this.

Of course, if you are frequenly deleting from the table as well, that doesn't hold up.

It's like a phone book, it's ordered by name so if you want to add a new person, you would have to rip your phonebook in 2 and add a page in between. But if a phonebook was ordered by who was added chronologically, you would just need to add a page at the end when you add a person.

"But if a phonebook was ordered by who was added chronologically, you would just need to add a page at the end when you add a person."

In the above sentence, you meant that chronologically means the order in which they occur. So, the order will be according to the order in which that particular row is added to the table.Do you think that i got your point?