• I agree with everything Sean said. I'll add to it by questioning the nonclustered indexes that you want to add. Why are you adding those. Do you know you need them or are you just adding an index on foreign key constraints? I wouldn't suggest adding nonclustered indexes without understanding how the queries are being built and whether or not you'll need an index. The constraints themselves won't act as indexes, but if SQL Server knows it can only ever get one row in a join due to an enforced constraint, it's much more likely to do intelligent things in the plan. You may still need indexes on those columns, but, again, I wouldn't put them in there until you prove that you need them and then you may need to make them a covering indexes, depending on the queries needed. So, no, I don't agree with recommendations 2 & 3 without a lot more information.

    Also, I don't see a natural constraint? Based on all the other columns in the table, is it OK if they're all exactly the same, numerous times? If so, your keys and constraints are fine (although your data is going to be messy). If not, you should either consider going with a natural key, or, you need to add a constraint to enforce the natural key.

    Finally, as for picking a clustered index, what is the most common access path to the data in this table? Frequently, that's the primary key. But not always. I'd define that immediately. You only get one clustered index, so it's the most important part of setting up a table.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning