Problem
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:
CREATE TABLE YOUR_TABLE (
YOUR_PK int PRIMARY KEY NONCLUSTERED
-- 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:
CREATE TABLE YOUR_TABLE (
YOUR_PK int PRIMARY KEY,
YOUR_CLUSTERED_KEY int NOT NULL UNIQUE CLUSTERED
-- 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 https://stackoverflow.com/questions/15051869/relationship-of-primary-key-and-clustered-index