I was querying an Order Header table and was coming up with confusing results (the result set had too many rows).  The table used an Identity column as the primary key.  The table also contained an order confirmation number column that should have been unique.  To make a long story short, the reason I was getting confusing results was because there were rows in the Order Header table that contained the same order confirmation number.  This happened when I imported a more recent version of the Order Header table from another database, but neglected to also import a more recent version of the table that controlled the generation of order confirmation numbers.  When I then created new orders, the new Order Header rows were created using older confirmation numbers, and as a consequence, duplicate order confirmation numbers were entered into the table.  This scenario would NOT have occurred if the order confirmation number was the primary key.  An error would have occurred when I tried to add a new order that had a confirmation number that already existed in the Order Header table.

Here are some links to choosing a primary key that I found interesting.

http://databases.aspfaq.com/database/what-should-i-choose-for-my-primary-key.html

http://weblogs.sqlteam.com/jeffs/archive/2007/08/23/composite_primary_keys.aspx

http://decipherinfosys.wordpress.com/2007/02/01/surrogate-keys-vs-natural-keys-for-primary-key/

http://www.mssqltips.com/tip.asp?tip=1600