Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

closed as primarily opinion-based by Mark Storey-Smith, RolandoMySQLDBA, Max Vernon, bluefeet, Paul White Dec 10 '13 at 3:08

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise.If this question can be reworded to fit the rules in the help center, please edit the question.

add comment

3 Answers 3

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.

share|improve this answer
    
Those who argue for natural keys typically do so from a position of philosophical purity, as is the case in the Simple Talk article you cited. In my (25+) years of experience, people who argue from this position are long on education and short on real-world experience. In the real world just about every natural key I've ever come across is subject to duplication and/or redefinition. There are very few cases outside of smallish code tables where it is practical to take the philosophical high ground regarding natural keys. –  Joel Brown Dec 9 '13 at 23:48
add comment

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.

share|improve this answer
2  
You are confusing primary key with clustered index. The unique index behind the primary key constraint does not have to be clustered. –  Greenstone Walker Dec 9 '13 at 19:52
add comment

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.

share|improve this answer
    
Wrong on all 3. First, this is a big discussion with a lot of people on both sides. Second, that is just wrong (he hotspot) and three - no need for a Guid PK, just use a unique field for the Guid (which is what we do for exactly this reason), while in the db you then still use the int. –  TomTom Dec 9 '13 at 13:45
1  
I agree with Joel. Sometimes the natural keys are subject to change or duplication. In these cases, obviously they are not good candidate for primary keys. –  Sky Dec 11 '13 at 3:09
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.