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)
It might be worth reminding people that not all products use indexes. For example, Teradata is based on hashing. The advantage of a hash is that no matter how big the table gets, a key can be located in at most two probes, with over 99% of them being found in only one probe. Tree structured indexes, on the other hand, require traveling down levels of indexes, to get to the leaf notes.