When should I use primary key or index?

Question

When should I use a primary key or an index?

What are their differences and which is the best?

Answer 1

Basically, a primary key is (at the implementation level) a special kind of index. Specifically:

• A table can have only one primary key, and with very few exceptions, every table should have one.
• A primary key is implicitly UNIQUE - you cannot have more than one row with the same primary key, since its purpose is to uniquely identify rows.
• A primary key can never be NULL, so the row(s) it consists of must be NOT NULL

A table can have multiple indexes, and indexes are not necessarily UNIQUE. Indexes exist for two reasons:

• To enforce a uniquness constraint (these can be created implicitly when you declare a column UNIQUE)
• To improve performance. Comparisons for equality or "greater/smaller than" in WHERE clauses, as well as JOINs, are much faster on columns that have an index. But note that each index decreases update/insert/delete performance, so you should only have them where they're actually needed.

Answer 2

Differences

A table can only have one primary key, but several indexes.

A primary key is unique, whereas an index does not have to be unique. Therefore, the value of the primary key identifies a record in a table, the value of the index not necessarily.

Primary keys usually are automatically indexed - if you create a primary key, no need to create an index on the same column(s).

When to use what

Each table should have a primary key. Define a primary key that is guaranteed to uniquely identify each record.

If there are other columns you often use in joins or in where conditions, an index may speed up your queries. However, indexes have an overhead when creating and deleting records - something to keep in mind if you do huge amounts of inserts and deletes.

Which is best?

None really - each one has its purpose. And it's not that you really can choose the one or the other.

I recommend to always ask yourself first what the primary key of a table is and to define it.

Add indexes by your personal experience, or if performance is declining. Measure the difference, and if you work with SQL Server learn how to read execution plans.