I am going to relate this topic to a development and writing of a book as most people at one time or another have thought about writing a book.
When initially thinking about a book you have a lot of ideas. They don’t necessarily flow in any order (although they may.) Since these ideas are popping into your head they are just a pile of ideas. Another term for pile is heap. They are just a heap of ideas. The ideas themselves are in no specific order but the orders they pop into your head are sequential.
This is what a heap table is. As each record is added to the table they are ordered one after the other. I won’t get into how they are referenced, just know they are added one after the other. So a heap table is all about when the records are added.
As you work on your outline for your book you begin to organize your thoughts. Instead of just writing your ideas down in a list you begin to order that list. You place ideas into sections then into chapters. You may even go further by creating a detailed outline based on time.
By physically ordering your ideas you are changing their position within the outline. This is what a clustered index does. If physically orders the data within the table. The last records added may become the first record in the table because it makes sense for it to be there. You book may be time based and thus time is the clustered index or your book my jump from time period to time period thus chapter becomes the clustered index. And just as you have a single outline, you can only have a single clustered index.
Finally, at the end of your book you are going to create an index. This index specifies where things (concepts) are in the book. Maybe your book is about people and there is someone who is references rarely yet you wish to let people know where he is referenced in the book. This is a non-clustered index. Where the person is referenced hops around. If you have a heap table then where you reference the person is not sequential. The first reference may be to when they die because that was what you’re first thought was. When they are born may be in the middle, again, because that was when you thought of it. And just as your book can have many concepts you can have many non-clustered indexes.
But if you have a clustered index then the first reference you find using a non-clustered index will be the first reference relative to the story line.
So, a heap table is all about the order of thought, or the order of entry into a table. A clustered index is all about the order of relevance and a non-clustered index is all about an idea contained within the context of the book or table.