SQL Server Performance

Indentity column and uniqe index on another column

Discussion in 'Getting Started' started by jan_bp, Apr 19, 2007.

  1. jan_bp New Member

    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!
  2. Adriaan New Member

    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).
  3. MohammedU New Member

Share This Page