What's the difference between a Primary Key and Identity?

Question

In a SQL Server db, what is the difference between a Primary Key and an Identity column? A column can be a primary key without being an indentity. A column cannot, however, be an identity without being a primary key.

In addition to the differences, what does a PK and Identity column offer that just a PK column doesn't?

Answer 1

A column can definitely be an identity without being a PK.

An identity is simply an auto-increasing column.

A primary key is the unique column or columns that define the row.

These two are often used together, but there's no requirement that this be so.

Answer 2

This answer is more of WHY identity and primay key than WHAT they are since Joe has answered WHAT correctly above.

An identity is a value you SQL controls. Identity is a row function. It is sequential either increasing or decreasing in value, at least in SQL Server. It should never be modified and gaps in the value should be ignored. Identity values are very useful in linking table B to table A since the value is never duplicated. The identity is not the best choice for a clustered index in every case. If a table contains audit data the clustered index may be better being created on the date occurred as it will present the answer to the question " what happened between today and four days ago" with less work because the records for the dates are sequential in the data pages.

A primary key makes the column or columns in a row unique. Primay key is a column function. Only one primay key may be defined on any table but multiple unique indexes may be created which simulates the primary key. Clustering the primary key is not always the correct choice. Consider a phone book. If the phone book is clustered by the primay key(phone number) the query to return the phone numbers on "First Street" will be very costly.

The general rules I follow for identity and primary key are:

1. Always use an identity column
2. Create the clustered index on the column or columns which are used in range lookups
3. Keep the clustered index narrow since the clustered index is added to the end of every other index
4. Create primay key and unique indexes to reject duplicate values
5. Narrow keys are better
6. Create an index for every column or columns used in joins

These are my GENERAL rules.