sql - Relationship of Primary Key and Clustered Index

Question

Can a TABLE have a primary key without a clustered index?

And can a TABLE have a clustered index without having a primary key?

Can anybody briefly tell me the relationship between primary key and clustered index?

Answer 1

A primary key is a logical concept - it's the unique identifier for a row in a table. As such, it has a bunch of attributes - it may not be null, and it must be unique. Of course, as you're likely to be searching for records by their unique identifier a lot, it would be good to have an index on the primary key.

A clustered index is a physical concept - it's an index that affects the order in which records are stored on disk. This makes it a very fast index when accessing data, though it may slow down writes if your primary key is not a sequential number.

Yes, you can have a primary key without a clustered index - and sometimes, you may want to (for instance when your primary key is a combination of foreign keys on a joining table, and you don't want to incur the disk shuffle overhead when writing).

Yes, you can create a clustered index on columns that aren't a primary key.