Can I have a primary key without clustered index ?

Question

Folks, I would like to understand the answer for the following questions:

1. Can I have a primary key without clustered index ? ( I am aware that when we create primary key constraint on a column, it by default creates a clustered index. So in that case, how should I deactivate clustered index ?)

2. Can I have a clustered index with multiple columns together ? (Like in non-clustered where I can join different columns for a single non-clustered index).

Answer 1

MySQL and SQL-SERVER are different RDBMS. They have different capabilities and different syntax.

When using InnoDB, MySQL always makes the PK the clustered index.

SQL-SERVER, however, will let you create a PK without the CLUSTERED keyword, and let you use it within another index instead.

In both cases, PrimaryKeys and Indexes (clustered or not) can cover multiple fields.

Answer 2

(This answer is for SQL Server 2005+ only. I know nothing about MySQL.)

---

Can I have a primary key without clustered index?

Yes. As you mentioned, a primary key constraint is backed by a clustered index by default. You can tell SQL Server to back the constraint with a nonclustered index by declaring the constraint as follows:

ALTER TABLE MyTable
    ADD CONSTRAINT PK_MyTable
        PRIMARY KEY NONCLUSTERED(Col1);

---

Can I have a clustered index with multiple columns together ? (Like in non-clustered where I can join different columns for a single non-clustered index).

Yes, you can define an index with more than one column in the index key. It's really no different than a nonclustered index.

CREATE UNIQUE CLUSTERED INDEX IX_MyTable_Clus
    ON MyTable(Col1, Col2, Col3);

---

References: ALTER TABLE, CREATE INDEX