Hugo Kornelis (4/20/2010)
I wanted to disagree with this at first, but since several other people have posted similar comments, I'll have to accept that, obviously, this was not clear enough.
Clear enough, possibly...I did after all understand what you were getting at...I just felt the wording could have been improved, and suggested an alternative.
However, I don't understand the argument that this depends on the number of rows in the table. Can you explain for what number of rows a SELECT based on WHERE PersonID = @PersonID will be faster without an index on PersonID, and why?
My difficulty is with the phrase "SQL Server does not have an efficient way to retrieve rows..." - I never said it could be faster without an index (though it could).
Including the trivial case where the table has one row, if the rows in the table fit on a single page, SQL Server has a perfectly 'efficient' way to retrieve the rows. That is why I said it depends on the number of rows.
I suppose I could argue that if the table were filled with very many rows having the same value for PersonID, an IAM-ordered scan of the heap might be more efficient than a partial scan of a very fragmented index, but that was not my original point...;-)
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi