Is Non-Clustered index with all others columns included equivalent to a Clustered Index

Posted on

Question :

Is a nonclustered index on a column with all other columns included almost like a clustered index on a different column? Will it actually duplicate the full Table?

Normally you might not need to do that because looking up the rows might be less costly than duplicating data but I am asking from a theoretical perspective.

I use the following database systems MSSQL,PostgreSQL and Oracle. Would it depend on different databases?

Answer :

The answer will vary according to your understanding of the word “equivalent”.

From a logical / application perspective they are the same. Both reference all columns of the table and define an ordering on all rows.

Physically the image on disk and the thread of execution through the server software are likely to be different. The optimiser may treat them differently, too.

Leave a Reply

Your email address will not be published. Required fields are marked *