title: What's the best practice for primary keys in tables?
body: <p>When designing tables, I've developed a habit of having one column that is unique and that I make the primary key.  This is achieved in three ways depending on requirements:</p>
  <ol>
  <li>Identity integer column that auto increments.</li>
  <li>Unique identifier (GUID)</li>
  <li>A short character(x) or integer (or other relatively small numeric type) column that can serve as a row identifier column</li>
  </ol>
  <p>Number 3 would be used for fairly small lookup, mostly read tables that might have a unique static length string code, or a numeric value such as a year or other number.</p>
  <p>For the most part, all other tables will either have an auto-incrementing integer or unique identifier primary key.</p>
  <h1>The Question :-)</h1>
  <p>I have recently started working with databases that have no consistent row identifier and primary keys are currently clustered across various columns.  Some examples:</p>
  <ul>
  <li>datetime/character</li>
  <li>datetime/integer</li>
  <li>datetime/varchar</li>
  <li>char/nvarchar/nvarchar</li>
  </ul>
  <p>Is there a valid case for this?  I would have always defined an identity or unique identifier column for these cases.</p>
  <p>In addition there are many tables without primary keys at all.  What are the valid reasons, if any, for this?</p>
  <p>I'm trying to understand why tables were designed as they were, and it appears to be a big mess to me, but maybe there were good reasons for it.</p>
  <p>A third question to sort of help me decipher the answers: In cases where multiple columns are used to comprise the compound primary key, is there a specific advantage to this method vs. a surrogate/artificial key?  I'm thinking mostly in regards to performance, maintenance, administration, etc.?</p>