Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key? Which one is the best choice for primary key in SQL Server?There are some example code:

Uniqueidentifiers

e.g.

CREATE TABLE new_employees
   (employeeId   UNIQUEIDENTIFIER      DEFAULT NEWID(),
   fname      VARCHAR(20) )
GO
INSERT INTO MyUniqueTable(Characters) VALUES ('Karin')
GO


Identity columns 

e.g.

 CREATE TABLE new_employees
 (
  employeeId int IDENTITY(1,1),
  fname varchar (20)
 );

 INSERT new_employees
    (fname)
 VALUES
    ('Karin');


[Material code](or Business Code,which identity of a material. e.g. customer identifier)

e.g.

CREATE TABLE new_employees(
    [ClientId] [varchar](20) NOT NULL,
    [fName] [varchar](20) NULL      
 )

 INSERT new_employees
    (fname)
 VALUES
    ('C0101000001',--customer identifier，e.g.'C0101000001' a user-defined code.
     'Karin');


Please give me some advice for choosing the primary key from the three type identity column,or other choices.

Thanks!
Fabian Pascal identifies four pragmatic criteria for choosing a key: familiarity, irreducibility, stability and simplicity. He also notes that sometimes you must make trade-offs.

During the logical design phase you will identify all the candidate keys for an entity.

During the physical design phase you will decide which of them will be the primary key (the one usually used in referential integrity, in other words, foreign keys). If you don't think any of the candidate keys are suitable for the primary key then you choose to use an arbitrary key, and then decide what mechanism will be used to generate the arbitrary key, perhaps the application generates it, perhaps the server does (identity, guid with newid, sequence, something else). If your systems will be multi-master in some way then application-generation might be better, as most of the server-side mechanisms don't handle this.

The choice of primary key is determined by size and type - you will be joining on it, so you want it to be small.

During the physical phase you will also be doing index design, which includes deciding which index will be the clustered index. You want that to be small as well, since the clustered index key is included in all non-clustered indexes. You want it to be unique, otherwise SQL Server will add a uniquifier, which has a small impact on performance.

On the other hand, this is not the 70s. We now have computers that don't fill entire warehouses and take all week to run the payroll. If won't kill your performance to use a guid or a char(8) either as a clustered key or as a primary key. Unless you are UPS or AIM Healthcare (two winners of WinterCorp's 2005 TopTen survey).

Edit: I just had this link arrive in my mailbox. Simple talk Primary Key Primer for SQL Server

Edit: Joel's comment below is very important. Natural keys often aren't stable. Even government-issued keys (tax numbers, drivers licence numbers) that should be static can change. For example, ask Mrs. Hilda Schrader Whitcher about her two SSNs. You control the arbitrary key so you control its stability.

In my (admittedly limited) experience, I can't remember a time we used a natural key. I've done work in healthcare, where there were government-issued id numbers (like NZ's Community Services Card) but not every person had them, so they were not suitable for PKs.