Basically the PK cloud be an IDENTITY used for lookups (it's unique, small, fast in joins).

But the clustered index should be on the column(s) most used in queries (WHERE statements).

In the example I gave, CustomerCode is used a lot in searches (lookup text boxes in the application) but not used in joins (for that we have the Id).

But CustomerCode is not sequential and can generate a lot of fragmentation on the clustered index.

Is it worth creating the cluster index on CustomerCode and have maintenance plan to rebuild/reorganize the indexes over creating the cluster on the Id (the only advantage is not having RID Lookups but Key Lookups - not much of an advantage...)?

Thanks,

Pedro