This is a very commonly asked interview question and often asked to confuse the candidate.
Q: Can we create clustered index without primary key? Is it possible to have clustered index on a column and primary key on another column in the same table?
Answer: Yes we can create the clustered index without primary key. Also, primary key and clustered index can be on different columns in the same table.
Justify your answer.
- Primary key is a constraint and clustered index is an index so, logically they are both different entities in RDBMS. As a matter of fact, this is possible in one condition, when clustered index is created before the primary key on the table otherwise primary key will by default create the clustered index with it and only one clustered index is allowed per table.
- Primary key does not allow NULL, where in clustered index allow NULLs.
- Clustered index without primary key creates an Unique, PRIMARY KEY NONCLUSTERED index on the table.
CREATE TABLE sqlindia (ID INT NOT NULL, EMPID INT null, EmpName VARCHAR(100) ) GO CREATE CLUSTERED INDEX sqlindia_EMPID ON sqlindia (EMPID) GO ALTER TABLE sqlindia ADD PRIMARY KEY (id) GO INSERT INTO sqlindia SELECT 1,NULL, 'Devi' UNION ALL SELECT 2,NULL, 'Prasad' UNION ALL SELECT 3,1, 'Sahoo'