Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key? [closed]

Question

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!

Answer 1

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.

Answer 2

Identity.

The PK should be SMALL - because it is also implicit part of ANY OTHER INDEX. It turns into basically the "id" of a row - so any other index needs to include it.

The Guid is a lot larger (and thus less efficient), and the naturaly (material code) turns that to a top, not only being large - ridiculously so - but also varchar which is slow in comparison.

My standard by now is to have an identity field as PK and possibly a GUID as "Identifier" (application level unique key), though I do go away from that regularly. But material codes has serious negative implications on performance if you deal with non-trivial amounts of data. And I regularly have multi billion row tables.

Answer 3

You should avoid using natural keys except under very specific circumstances. A natural key is visible to a user and in most cases is subject to change or be duplicated. You can deal with a primary key changing, but you don't want to. An exception to this is something which is strongly regulated by an independent body, like country codes or currency codes. In such cases the risk of a change may be worth the reward of keeping things a little simpler by using a natural key as a foreign key.

Beyond that, when choosing a surrogate key you should use the simplest and smallest one which works for you. This usually means an integer (IDENTITY). Sometimes IDENTITY is an issue because of insert hot-spots, in which case a random integer may be necessary, but that is a special case.

If you create records in multiple (distinct) databases and then need to consolidate these records into a single table then you may want to use GUID as your primary key, since you don't have to worry about key collisions in the consolidated table with a GUID primary key.