September 20, 2016 at 9:20 am
GilaMonster (9/20/2016)
Mike Scalise (9/20/2016)
However, if I add only a non-clustered index with ID as the key, then the table becomes ordered, much like it would if I created a clustered index on ID. I just tested it, and it seemed to work this way, unless I'm missing something.No it doesn't. The heap is still an unordered heap. The index is logically ordered by the ID. What you're missing is that your testing query likely used the nonclustered index, which is logically ordered. Force the query to use the heap ( WITH (INDEX = 0)) and you won't see results ordered by the index key.
I see what you mean. You're right...
Mike Scalise, PMP
https://www.michaelscalise.com
September 20, 2016 at 9:46 am
I had this exact discussion with Adam Machanic once. We came down on the side, apart from index maintenance and storage (and we're ignoring some pretty big issues), as far as the query optimizer and performance is concerned, a clustered index and a non-clustered index with all the columns INCLUDEd at the leaf level (assuming they can be, data types, etc., there are wrinkles in all this), they are the same. There is fundamentally no difference.
Now, you add in everything that has been raised, duplicate storage, heap vs. cluster, etc., and there are huge differences. Still, there is that moment where they can be thought of as identical. It's a B+ tree plus data at the leaf, either way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 16 through 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply