Unless I am missing something there?
Basically what it means is that the scan count can't be trusted as not all operators set it in the same way.[/quote]
I'm inclined to agree - though I'd love to try a repro script for it!
GilaMonster (4/2/2009)
Not at all. Seeks can return any number of rows, up to the total number of rows in the table.It's a seek if SQL used the b-tree to find the row or the start or end of the range. It's a scan if the entire table/index is read with no search.
See - http://sqlinthewild.co.za/index.php/2009/03/05/when-is-a-seek-actually-a-scan/
Not so sure. The article you wrote simply shows that a range seek/scan can cover all the rows in a table. Well, yeah 😉
The 'Index Seek' operator in that example has a tool-tip description of "*scan* a particular range of rows from a nonclustered index". So it's a seek logical operator doing a physical scan of a portion of the index? Even a full scan of an index is a seek operation (ordered or unordered) down the b-tree. To complicate things further, a single-row seek done as part of a key lookup operation (if the QO estimates that it will access many rows eventually) may implement ordered or unordered prefetch, which is a scan, isn't it?
I guess my point is that it is a complex area, and probably just a question of semantics - are we talking logical or physical operations, for example? What is the real different between a load of consecutive single-row seeks and an ordered scan?
I love SQL Server 😀
-- edited cos I can't quote properly...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi