Identity/Primary Key columns pattern for performance in SQL?
On tables with a large number of rows being inserted having a ever-increasing clustered index generally improves write performance as it ensures that new records are added together "at the end" of the table. (Note that clustered indexes and primary keys are not the same thing, you can actually have a different primary key from your clustered index. Its the clustered index which matters here, not the primary key).

If you have another auto-incrementing column in your table then you could use this as your clustered index and keep your primary key the same, however your clustered index needs to be unique (so you can't use a DATETIME), which generally means an auto-incrementing column, which may as well be your primary key.

That said, you should run some performance / stress tests and try it for yourself. In the past I worked on a system which intentionally clustered on a GUID on a frequently updated table, the theory being that spreading out the writes actually reduced lock contention and improved performance (I wasn't part of that change so to be honest I was always kind of skeptical). Try it out and see which is faster with your data.



To answer your second question, looking up a user by its username should be very fast with proper indexing - its possible that clustering the the username could improve performance, however its going to be very marginal.