Indentity column and uniqe index on another column | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Indentity column and uniqe index on another column

Hi, Just a quick question, if I have a table where a username (that must be unique) and some other information for user is stored, should I use the username as the primary key (because it must be unique) or should I use another identity column as primary key and just add a unique index on username. The username column will be varchar and can thus not be sequential. Any advise would be great!
Correct! For the primary key, you can choose either the identity or the varchar column. It depends on whether you will be using either one as FK constraint on other tables. The issue is more about what becomes the clustered index: if your table does not have one when you set the primary key, and you do not mention that the PRIMARY KEY should be NONCLUSTERED, then the primary key is created as the clustered index. So if you choose the varchar column to be the primary key, make sure that the identity column has a unique constraint, and that it already has a clustered index (because of the sequential nature of identity columns).
The Clustered Index Debatehttp://www.sqlskills.com/blogs/kimberly/2007/02/02/TheClusteredIndexDebateContinues.aspx
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
]]>