Folks, I would like to understand the answer for the following questions:
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 ?)
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).
(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
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With