I do agree that primary key clustered is not a good suggestion for an identity column, instead this can be done as primay key nonclustered and other valid column with clustered index. (so, everything wud be a index seek) One doubt here, suppose it is an retail bank customer search, we ll have to take last name for clustered....:w00t: Your suggestions please...

Also, thanks for the information regarding the PAD_INDEX... as it says it does a defragmentation at the leaf level alone, if it is OFF. But i guess, this will not solve the problem as there will be empty space in all the data pages which will be unused other than the last data page.

Now, one more question here, if we are having identity column as primary key clustered and sme other columns (say 2) as non clustered indexes, is it OK that we do a INDEXDEFRAG instead of DBREINDEX, if the environment is big? and of course a scheduled DBREINDEX in 4 or 6 months... I know i am asking too much... this wud be the last for index... :hehe: