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?
Advertisement
Answer
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.