Marios Philippopoulos (2/19/2008)
You also may want to consider making your primary key ID column non-clustered and define your clustered index on a more suitable column, eg. a record timestamp.Queries involving a *range* of values on a certain column/columns, such as a datetime range, benefit greatly from a clustered index defined on that column/columns.
Defining your PK as non-clustered means that page splits due to table UPDATES will NEVER happen - an update of a varchar column will not affect the stacking of the PK pages.
Personally, I find that defining an identity column PK as clustered is wasting a precious resource, unless this is the ONLY index you think you will EVER need on that table.
Must need some more coffee- but I fail to see how the choice of the clustered index is ever going to prevent page splits due to updates of varchars. If a row is in a page, and its varchar column is updated from holding 20 characters to 5000 characters, then it's going to cause a page split if the page doesn't have enough room to accomodate the extra 4980 characters.
Also - the PK has nothing to do whatsoever with page splits. The Clustered index does on the other hand.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?