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

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?

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.

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.

A primary key (also known as a candidate key) is any set of attributes that have the properties of uniqueness and minimality. That means the key column or columns are constrained to be unique. In other words the DBMS won't permit any two rows to have the same set of values for those attributes.

The IDENTITY property effectively creates an auto-incrementing default value for a column. That column does not have to be unique though, so an IDENTITY column isn't necessarily a key.

However, an IDENTITY column is typically intended to be used as a key and therefore it usually has a uniqueness constraint on it to ensure that duplicates are not permitted.

Major Difference between Primary and Identity Column

Primary Column:

• Primary Key cannot have duplicate values.
• It creates a clustered index for the Table.
• It can be set for any column type.
• We need to provide the primary column value while inserting in the table.

Identity Column:

• Identity Column can have duplicate value.
• It can only be set for Integer related columns like int, bigint, smallint, tinyint or decimal
• No need to insert values in the identity column. It is inserted automatically based on the seed.

EDITS MADE BASED ON FEEDBACK

A key is unique to a row. It's a way of identifying a row. Rows may have none, one, or several keys. These keys may consist of one or more columns.

Keys are indexes with a unique constraint. This differentiates them from non-key indexes.

Any index with multi-columns is called a "composite index".

Traditionally, a primary key is viewed as the main key that uniquely identifies a row. There may only be one of these.

Depending on the table's design, one may have no primary key.

A primary key is just that - a "prime key". It's the main one that specifies the unique identity of a row. Depending on a table's design, this can be a misnomer and multiple keys express the uniqueness.

In SQL Server, a primary key may be clustered. This means the remaining columns are attached to this key at the leaf level of the index. In other words, once SQL Server has found the key, it has also found the row (to be clear, this is because of the clustered aspect).

An identity column is simply a method of generating a unique ID for a row.

These two are often used together, but this is not a requirement.

You can use IDENTITY not only with integers, but also with any numeric data type that has a scale of 0

primary key could have scale but its not required.

IDENTITY, combined with a PRIMARY KEY or UNIQUE constraint, lets you provide a simple unique row identifier

Primary key emphasizing on uniqueness and avoid duplication value for all records on the same column, while identity provides increasing numbers in a column without inserting data. Both features could be on a single column or on difference one.