Ananth (12/10/2007)
whether index scan reads the data from index or table? if it reads from the table then there is no advantage of this index scan because , it is reading the data in the normal manner that is from the table and performance will not be improved......If i'm misleading, correct me!!!!!
An index scan is a complete scan of all the pages in a non-clustered index.
A clustered index scan is a complete scan of all pages in a clustered index (ie, the table itself)
Neither scan uses the b-tree structure of the index, but just reads the leaf pages in order, using each page's reference to the next in the chain.
An index seek is a seek through the b-tree structure of a non-clustered index, from the root down to the leaf.
A clustered index seek is a seek through the b-tree structure of a clustered index, from the root down to the leaf.
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