I'm trying to comprehend the difference between an allocation scan in a heap and in a B-tree, performance-wise.
Let's say, that we have an Orders table, with a clustered index defined on the OrderDate column.
If we query it like so:
SELECT orderid, custid, empid, shipperid, orderdate, filler FROM dbo.Orders;
The execution plan will consist of a Clustered index scan with "Ordered" property valued "False", which (if I understand correctly) leaves the storage engine with two options on how to perform it:
- With an index order scan, starting with the first leaf and proceeding in the order of the index
- With an allocation scan, starting with the first IAM and proceeding in the file order
The question is: Will there be a difference between the second option and the situation when the Orders table is a heap (hence the storage engine can only use an allocation scan)? And if not, is there any advantage in creating indexes on tables, that would mostly be queried without ordering?
Or I just got the whole thing wrong and it doesn't work that way.