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?

  • 2
    @Nishant Postgres for example does not have clustered indexes, so the question would be mute there. SQL Server and MySQL (InnoDB) have, but the answer may differ in details. Oracle has (they call them Index Organized Tables) but I don't know the details, they may differ a lot from SQL Server implementation. Oh and the TokuDB engine in MySQL, which allows multiple clustered indexes per table. Nov 10 '16 at 15:46

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.

  • I guess it's a different question, but have you seen any good usages of that in the wild? By good I mean "there are no obvious better alternative".
    – vorou
    Sep 17 '18 at 16:50
  • 1
    @vorou The example that springs to mind is covering indexes on very narrow tables that have multiple access patterns e.g. lookup by natural key, lookup by surrogate key. I can't recall a time when I've duplicated the whole table, however. Sep 18 '18 at 3:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.