How to Create Primary Key Without ANY Index? – Interview Question of the Week #143

Question: How to Create Primary Key Without ANY Index?

Answer: Before you jump and say you know this answer, I request you to read the question one more time.

In SQL Server, when we create Primary Key Constraint, it automatically creates Clustered Index by default in the background.

CREATE TABLE TestTable
(ID INT NOT NULL PRIMARY KEY,
Col1 INT NOT NULL)
GO

For example, run following script and you will notice that it will create a clustered index on the PK column.

We can also create PK with a non-clustered index by specifying NONCLUSTERED keywords. Here is the example of the same script.

CREATE TABLE TestTable1
(ID INT NOT NULL PRIMARY KEY NONCLUSTERED,
Col1 INT NOT NULL)
GO

Now the question is – Can I create a Primary Key without any index. We want neither clustered index nor non-clustered index. We want is just the primary key without any index – is that even possible?

The answer is NO.

It is not possible at all.

If I have to say in the most simple words, Primary Keys exists with either Clustered Index or Non-Clustered Index. 

If you drop the constraint, it will also drop the index along with it and if you drop the constraint, it will also drop your index. The matter of the fact, the script which you generate to drop the index, will generate drop constraint script only.

I guess, that answers this question. If you have any further insight, please add a comment here. I am sure there is more wisdom exists on this subject and the world would love to know more about it.

Reference: Pinal Dave (https://blog.sqlauthority.com)