weberharter (11/26/2012)
select top 1000 * from #TestIndexAccess
I still have a clustered index scan but it is NOT an ordered scan. Still the first query needs only 3 logical reads. I don't think it did a full scan.
No, it didn't do a full scan. The operator's the same, you can tell partially from the number of rows outputted and the reads done. It's still a scan, not a seek
p.s. It's not an ordered scan because there's no order by on your query, hence no requirement for the data to be in any particular order. Put an Order By <clustered index column> and you'll get an ordered scan.
Ordered scan = scan the index in order and preserve the order
Unordered scan = scan the index and don't worry about the order
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability