Identity Columns Aren’t Automatically Primary Keys

Identity columns (automatically incrementing numbers) are fairly common in SQL Server, having the merit of simplicity and pretty good performance. As such they are often used as primary keys, and that’s fine too (unless you subscribe to natural keys at all costs). But, they aren’t automatically primary keys. If you want it to be the primary key, make it the key.

Why?

Without a primary key there is no guarantee that the column will remain unique. It’s entirely possible – if unlikely – that someone will update one of those nice simple integers and in the process create a duplicate, or perhaps null out one of them, causing some mild chaos.

My advice? Don’t mix the two concepts. Every table gets a primary key declared. If you want it to be an identity, a guid, or a natural key, that’s fine, but it’s a detail. Define the key, you’ll save yourself some pain later on.