深度探索:Clustered Index ScanvsTable Scan .有帖子http://topic.csdn.net/u/20080704/11/40975992-fcce-
深度探索:Clustered Index Scan vs Table Scan . 有帖子http://topic.csdn.net/u/20080704/11/40975992-fcce-4807-bd65-b65cc0b39e09.html 提到索引扫描的问题，其中关tb上只建有聚集索引时，select * from tb到底是走聚集索引还是走iam， 还是有些疑虑，所以做了以下的测试，有经验的同学可以发表下意见。
环境: SQL 2005+SP2 ON Winxp SP3
SQL code
1.use tempdbgocreate table tb(a int primary key,b char(5000) default('bbbbb'))godeclare @i int,@v intset @i=1while @i<=400beginreval: set @v=abs(checksum(newid())%1000) if exists(select * from tb where a=@v) goto reval insert into tb(a) values (@v) set @i=@i+1endgo
2.找出IAM、Root、IAM指向的第一页的页面位置
SQL code
select [first],[root],firstIAM,* from sysindexes where id=object_id('tb')
打开set statistics io on ， 发现select * from tb with (nolock)比select * from tb 少读取1个io，应该是略过了根索引页。
PS:下面的一段文字和图片都是来自於Inside SQL Server 2005这本书，说的是聚集索引扫描会走iam，这样看来是有问题的。 even though the execution plan shows a clustered index scan, the activity is no different than a table scan, and throughout the book I will often refer to it simply as a table scan. As shown in the illustration, here SQL Server will also use the index's IAM pages to scan the data sequentially. The information box of the Clustered Index Scan operator tells you that the scan was not ordered, meaning that the access method did not rely on the linked list that maintains the logical order of the index.
[解决办法] 在《Inside Microsoft SQL Server 2005 T-SQL Querying》一书中，作者讲解了Index Access Methods。 其中对于Unordered Clustered Index Scan的讲解确实是有不正确的地方，正如楼主所迷惑的。 此书出版不久，作者就在他的Blog上重新阐述了这个问题。
参考： Quaere Verum - Clustered Index Scans - Part I http://www.sqlmag.com/Article/ArticleID/92886/sql_server_92886.html Quaere Verum - Clustered Index Scans - Part II http://www.sqlmag.com/Article/ArticleID/92887/sql_server_92887.html Quaere Verum - Clustered Index Scans - Part III http://www.sqlmag.com/Article/ArticleID/92888/sql_server_92888.html
