Coder Perfect

Primary Key and Clustered Index Relationship


Is it possible for a TABLE to have a primary key without using a clustered index?

Is it possible for a TABLE to have a clustered index without a primary key?

Can somebody explain the relationship between primary key and clustered index in a few words?

Asked by F11

Solution #1

A primary key is a logical concept: it’s a table row’s unique identifier. As a result, it has a number of properties: it can’t be null, and it has to be unique. Of course, because you’ll be searching for entries by their unique identifier a lot, having an index on the main key is a good idea.

A clustered index is a physical concept that influences how entries are stored on storage. When reading data, this makes it a highly quick index, yet it may slow down writes if your primary key isn’t a consecutive number.

Yes, you can have a primary key without a clustered index, and you may want to do so in some cases (for example, if your primary key is a mix of foreign keys on a joining table and you don’t want to suffer the disk shuffle expense while writing).

Yes, a clustered index can be created on columns that aren’t main keys.

Answered by Neville Kuyt

Solution #2

A table can have a primary key that is not clustered, and a clustered table does not require a primary key. As a result, yes to both queries.

All columns are stored at the leaf level in a clustered index. A clustered index, then, contains all of the data in the table. A table without a clustered index is called a heap.

A primary key is a clustered index with a single value. When you construct a primary key, if the table isn’t clustered yet, the primary key will be produced as a clustered unique index by default. Unless you specify the nonclustered option directly.

In the following scenario, t1 has a nonclustered primary key and t2 does not have a clustered primary key but does have a primary key:

create table t1 (id int not null, col1 int);
alter table t1 add constraint PK_T1 primary key nonclustered (id);
create clustered index IX_T1_COL1 on t1 (col1);

create table t2 (id int not null, col1 int);
alter table t2 add constraint PK_T2 primary key nonclustered (id);

SQL Fiddle has a good example.

Answered by Andomar

Solution #3

Take a look at Index-Organized Tables and Clustered Indexes, for starters. To truly grasp what’s going on, I recommend reading the entire Use the Index Luke! site from the beginning until you reach the clustering topic.

Now for the answers to your queries…

To create a heap-based table, utilize the NONCLUSTERED keyword when declaring your primary key. Consider the following scenario:

    -- Other fields...

This is unfortunate, because many people appear to accept the default (CLUSTERED), even though a heap-based table would be better in many circumstances (as discussed in the linked article).

Unlike some other DBMSes, MS SQL Server allows you to have a clustering index that is not the same as the primary key, or even if the main key is not there at all.

The following example produces a clustering index that is distinct from the PK and includes a UNIQUE constraint on top of it, which is likely what you’d want in most cases:

    -- Other fields...

If you create a clustering index that isn’t unique (using CREATE CLUSTERED INDEX…), MS SQL Server will make it unique by adding a hidden field.

Please keep in mind that clustering has the greatest impact on range scans. You’re effectively undermining the point of clustering if you employ a clustering index that doesn’t “align” with range scans performed by your client application(s) (for example, when over-relying on the hidden column discussed above, or clustering on a surrogate key).

Primary keys are clustered by default in MS SQL Server. As previously mentioned, you can modify the default.

Answered by Branko Dimitrijevic

Solution #4

MSDN’s Using Clustered Indexes provided the answers.

Is it possible for a TABLE to have a primary key without a Clustered Index? – Of course.

Is it possible for a TABLE to have a Clustered Index without a primary key? – Of course.

A Primary Key is a constraint that ensures uniqueness of the values, such that a row can always be identified specifically by that key.

A main key is automatically assigned an index (as rows are often “looked up” by their primary key).

A non-clustered index is a way of logically ranking rows based on one (or more) of its columns. Consider it a “clone” of the table that is arranged by the index’s columns.

When a table is physically ordered by a column, it is called a clustered index. A clustered index is not always present in a table (i.e., while it will be physically ordered by something, that item may be undefined). Although a table can have more than one clustered index, it can only have one composite clustered index (ie the table is physically ordered by eg Surname, Firstname, DOB).

The PK is a clustered index most of the time (but not always).

Answered by Sepster

Solution #5

For what it’s worth, all columns in the primary key in MS SQL Server must be specified as NOT Null, whereas building a unique clustered index does not. However, I’m not sure about other database systems.

Answered by robotj

Post is based on