Relationship of Primary Key and Clustered Index

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?

Solution 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.

Solution 2:

A table can have a primary key that is not clustered, and a clustered table does not require a primary key. So the answer to both questions is yes.

A clustered index stores all columns at the leaf level. That means a clustered index contains all data in the table. A table without a clustered index is called a heap.

A primary key is a unique index that is clustered by default. By default means that when you create a primary key, if the table is not clustered yet, the primary key will be created as a clustered unique index. Unless you explicitly specify the nonclustered option.

An example, where t1 has a nonclustered primary key, and t2 is not clustered but has 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);

Example at SQL Fiddle.

Solution 3:

First of all, take a look at Index-Organized Tables and Clustered Indexes. Actually, I recommend reading the whole Use the Index Luke! site from the beginning until you reach the clustering topic to really understand what's going on.

Now, to your questions...

Can a TABLE have primary key without Clustered Index?

Yes, use NONCLUSTERED keyword when declaring your primary key to make a heap-based table. For example:

CREATE TABLE YOUR_TABLE (
    YOUR_PK int PRIMARY KEY NONCLUSTERED
    -- Other fields...
);

This is unfortunate, since a lot of people seem to just accept the default (which is CLUSTERED), even though in many cases a heap-based table would actually be better (as discussed in the linked article).

and Can a TABLE have Clustered Index without primary key?

Unlike some other DBMSes, MS SQL Server will let you have a clustering index that is different from primary key, or even without having the primary key at all.

The following example creates a clustering index separate from the PK, that has a UNIQUE constraint on top of it, which is what you'd probably want in most cases:

CREATE TABLE YOUR_TABLE (
    YOUR_PK int PRIMARY KEY,
    YOUR_CLUSTERED_KEY int NOT NULL UNIQUE CLUSTERED
    -- Other fields...
);

If you choose a non-unique clustering index (using CREATE CLUSTERED INDEX ...), MS SQL Server will automatically make it unique by adding a hidden field to it.

Please note that the benefits of clustering are most visible for range scans. If you use a clustering index that doesn't "align" with range scans done by your client application(s) (such as when over-relying on the hidden column mentioned above, or clustering on a surrogate key), you are pretty much defeating the purpose of clustering.

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

Under MS SQL Server, primary key is also clustered by default. You can change that default, as discussed above.

Solution 4:

Answers taken from MSDN Using Clustered Indexes

Can a TABLE have primary key without Clustered Index? - Yes.

Can a TABLE have Clustered Index without primary key? - Yes.

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

An index is automatically assigned to a primary key (as rows are often "looked up" by their primary key).

A non-clustered index is a logical ordering of rows, by one (or more) of its columns. Think of it as effectively another "copy" of the table, ordered by whatever columns the index is across.

A clustered index is when the actual table is physically ordered by a particular column. A table will not always have a clustered index (ie while it'll be physically ordered by something, that thing might be undefined). A table cannot have more than one clustered index, although it can have a single composite clustered index (ie the table is physically ordered by eg Surname, Firstname, DOB).

The PK is often (but not always) a clustered index.

Solution 5:

For what it may be worth, in MS SQL Server all columns in the primary key must be defined as NOT Null, while creating unique clustered index does not require this. Not sure about other DB systems though.