SQL Server - Selecting a Primary Key for my table

I have a table that stores user info.

In the User table, username开发者_开发技巧 is unique. Do you think I should make username as primarykey or should I use a surrogate key that is an int?

Would using a string key hit performance badly?

---

Use a surrogate integer key.

Usernames won't change that often, but they could.

As to performance, don't worry about that until you know you have a problem.

SQL Server will create the clustered index on the Primary key column by default. If you use a wide key in the clustered index, all non-clustered indexes will also contain that wide key.

---

Generally using an int as the primary key. This is due in part to convention as well as saving space when using them as foreign keys in other tables. In reality, using your username field as the primary wouldn't hurt performance unless you wind up having thousands of records in multiple tables using it. If you think your tables will remain small, its up to preference.

---

I would use an identity surrogate primary key and cluster on that. The clustered index is included in all indexes and should be narrow, static and increasing.

As far as a primary key, you COULD make the username the primary key, BUT since foreign keys will reference it, you also want it to be static (which a username is not). So I would make a non-clustered unique index on username. The identity PK will automatically be included in the NCI.

I would include any other columns in that same index (as included columns) depending upon usage patters where access is primarily by username - for instance, the password hash, maybe the name. But I'd check the execution plans, use the profiler and/or the index tuning wizard with expected workloads.