Is retrieving rows using a non clustered index in a table with another index that is clustered, slower than retrieveing rows from a table with no clustered indexes what so ever?
If I understand things correctly, when you look up a non clustered index in a table with clustered indexes, SQL Server finds the cluster key, so that it can use the clustered index to find the data. So, it seems that retrieval by non clustered indexes would be faster when there are no clustered indexes at all.
(To clarify: Of course using clustered indexes to retrieve data is faster, but that's not the question here.)
I don't think your understanding is correct; some of what you say above implies you have some things wrong and much of it I am afraid does not make much sense to me.
However, to answer the question: Yes, SQL Server typically needs to read less pages to retrieve the data from a heap (a table without a clustered index) than from a clustered index when using a non-clustered index.
I've tried to edit the question, still not sure it's clear or correct though.
I thought it was something like Look for "foo" in non clustered index -> found a pointer to the page where the data is -> load the page and get the data
for heaps, compared to Look for "foo" in non clustered index -> found the cluster key "bar" -> look for "bar" in clustered index -> found the data
for tables w clustered indexes.
Anyway, you're answer is what I was looking for, thx!
Your explanation in your last post is pretty good.
Note that there are a lot more considerations than just this when it comes to comparing clustered indexes and heaps - it would not be right to say that heaps are superior to clustered indexes. It's only in particular circumstances that you would prefer a heap.