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.

I've tired to explain to every new junior developer in our r&d team why he should use Primary Keys and how to do that. So I decided to write small whitepaper, which every new developer should read. Here is the draft of it.

DISCLAIMER:

  1. I know and understand the difference between clustered index and primary key
  2. In the following question PRIMARY KEY means PRIMARY KEY CLUSTERED, and WITHOUT PRIMARY KEY means WITHOUT PK AND CLUSTERED INDEX
  3. Note that it is a whitepaper for junior and not SQL programmers
  4. All the things that they do - reviewed before entering to the main development branch
  5. I'm not going to explain to them - when there will be benefit of using clustered index and NONclustered primary key - else I will fall to premature optimizations hell

The question is - what else should I add to the document? May be - what I should change in it? And what to explain more in detail?

Here comes the draft:

  • Primary keys have to constrain any table within database.
  • Without PK a table considered as a heap and SQL Server has very limited uses of this type of data - the only thing I should say it is suitable - a buffer for fast bulk-loading data from the outside of SQL Server's engine.
  • Avoid using natural primary keys. Primarily because of theirs natural (gauss) distributions. For example in PhoneBook table with primary key based on Family and Name will be many Smiths and Wilsons and much fewer Zimmerbergs and this states, that pages containing Smiths and Wilsons will be splitted more often, than other pages, and queried also more often - which multiplies the performance impact. That leads to performance degradation, because of primary key's page fullness and most of searches will hit PK's sparsed pages.

Moreover, even using SSN or ID Number, which have (I hope) flat distribution, as PK does not solve the problem of PK's page splitting, because this numbers are not in any organized order.

  • Secondary - natural PKs are often composite. That creates composite foreign keys and wide indexes and as a result hurts performance. So, ...
  • Avoid using composite PKs. Better using simple surrogate PK and composite UNIQUE index, than all-in-one composite PK because it leads to composite FK and wide indexes due to statement, that every secondary index on table with PK has to include whole PK within.
  • Avoid using surrogate primary keys other than integer or uniqueidentifier types
  • During database design phase it is very important to identify entities and corresponding tables which may have in perspective "zillions" of rows, or tables which keys have to be NOT ONLY table-wide unique, but DB or even world unique, or which have to be joined with other tables by this key over several hop-tables - this tables better to have UUID PK, others - ordinary integer, because SQL Server very well fine tuned to use integer PKs.
  • These two types can guarantee both vector distribution (IDENTITY or NEWSEQUENTIALID()) and monotonous sequence.
  • Main database design rule - Twenty minutes spent to well thought-out design will save days or even weeks during production database's maintenance
share|improve this question
5  
"Without PK a table considered as a heap" is not correct. PK and "clustered index" are not synonyms. Also you can create FK constraints on any unique index so if you have a natural key and a surrogate key SQL Server doesn't care which, if any, is designated as the PK. –  Martin Smith Jan 9 '12 at 9:25
    
@MartinSmith Agree, but the performance on natural and surrogate keys are usually differs, so if I say, that FKeys on surrogate PKeys are usually performs better - it looks like true, right? // I'll update a question in a short –  Oleg Dok Jan 9 '12 at 9:40
2  
All of your points are wrong because they apparently refer to the properties of clustered indexes and not primary keys. Clustered indexes and primary keys are two different things. –  sqlvogel Jan 9 '12 at 10:34
1  
It is premature optimization to allow the choice of clustered index to dictate the primary key of your tables. Very premature and very unnecessary! –  sqlvogel Jan 9 '12 at 12:37
2  
Funny, I use the PhoneBook table example in the opposite way: if phone_number is a simple candidate key then consider making the superkey (family_name, phone_number) the clustered index if the table is primarily used for queries such as "get me all the numbers for people named 'Wilson'`. But I certainly don't encourage folk to follow rules rather than think for themselves ;) –  onedaywhen Jan 9 '12 at 16:23
add comment

3 Answers

up vote 7 down vote accepted

Your points are unrelated to database design: choice of natural or surrogate key is an implementation decisions after conceptual and logical models are complete

In addition to comments and other answers:

  • some natural keys work well such as currency or language codes (CHF, GBP, DE, EN etc)
  • avoiding composite keys forces you to always join intermediate tables (rather than simple) parent-grandchild
  • adding a surrogate key in unnecessary for link tables

Edit: example of "composite keys"

Assume: t1 has child t2 has child t3

  • If you had the key of t1 in t3 (composite key) you can join t1 and t3 directly.
    t1 key is also the left hand column of t3 key so you don't need an extra index

  • With a surrogate key/FK, you have to join via t2
    You need extra indexes on the FK columns in t2 and t3 which

This latter option with the "always use surrogate key" dogma

  • adds complexity
  • decreased or reverses disk space "savings"
share|improve this answer
    
yes, surrogate key is not needed for fork tables. Can you explain a little bit more your second point - probably because of my poor English its not so clear to me –  Oleg Dok Jan 9 '12 at 11:41
    
What about currency codes etc. - its very small tables, as I think, and with these tables we'll not meet the performance pitfalls described in the question, am I right? –  Oleg Dok Jan 9 '12 at 11:43
    
@OlegDok: you may use that currency code in a table with a "zillion" rows. It matters then (when you could gave tinyint not char(3)) but I'd still use char(3) because it's easier –  gbn Jan 9 '12 at 11:45
    
just wanted to ask about tinyint vs char(3) 8-) ok, that is also the point. Corrected the question, added DISCLAIMER –  Oleg Dok Jan 9 '12 at 12:03
add comment

You are confusing various concepts here, the major error being primary key != clustered key. Because of this misunderstanding, the majority of the guidance is incorrect. To be brutally honest, you are probably not well placed to be writing these guidelines.

  • Primary key != clustered key
  • Unique identifiers make for very poor clustered keys
  • SQL Server is not "very well fine tuned to use integer PKs". The case for a narrow clustered key is covered in the link above, it is not a matter of SQL Server being optimised for integer clustered keys.
share|improve this answer
    
Yes, I know the difference between pkeys and clusts, I'll refine the question to show my point of view –  Oleg Dok Jan 9 '12 at 11:44
    
Random UUIDs is one of the worst choices for PK, sequential is better, but not so good as integers and not so bad at all, as naturals like Name, LastName, MiddleName etc. I realize that, or I missed smth? –  Oleg Dok Jan 9 '12 at 11:46
    
And about "very well fine..." - ok, if I say it in this way: Dealing with integral types IN GENERAL is less expensive and adds less overhead, than character types in primary clustered keys –  Oleg Dok Jan 9 '12 at 11:49
    
I'm not sure that you do understand the difference between a PK and a clustered key, as demonstrated by your second comment. Might be easier to continue this in chat. –  Mark Storey-Smith Jan 9 '12 at 11:59
add comment

Why not start with the fundamentals of normalization and the ability to enforce relational integrity?

Also, if you are in an environment that uses transactional replication and there is any chance this table will ever be replicated, you'll need a primary key.

Here are some suggested reading materials you can share with these junior programmers:

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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