The role of key Hash value in SQL Server (top)

Test environment: SQLSERVER2005 Developer version

I'm really sorry, I did not find the answer to the question when I did the experiment.

The problem is this:

When looking through a clustered index lookup and a nonclustered index, the hash code is used to match, and the corresponding record is found

Since the hash code is used to match, then a hash bucket is required to load all key/value of all index pages into the hash bucket

Now that you want to load all the hash bucket, you need to read all the index pages.

My test script, I used set STATISTICS IO on to test if there was a reading of the index page, but at the end I couldn't find the rule

--sql How to find the hash value under the clustered index use master go-new database Iamdb Create DB scandb go use scandb   
Go--drop table clusteredtable--drop table Nonclusteredtable--Set up a test table CREATE TABLE clusteredtable (c1 int IDENTITY (1,1), C2 VARCHAR (900)) Go CREATE TABLE nonclusteredtable (c1 int IDENT ity (1,1), C2 VARCHAR (900)) Go--Indexing create CLUSTERED index cix_clusteredtable on clusteredtable   
([C2])   
Go CREATE INDEX ix_nonclusteredtable on nonclusteredtable ([C2]) Go--insert test data DECLARE @a INT;   
SELECT @a = 1;   
    while (@a <=) BEGIN inserts into Clusteredtable VALUES (CAST (@a as NVARCHAR (2)) +replicate (' A ', 880))   
SELECT @a = @a + 1 end DECLARE @a INT;   
SELECT @a = 1;   
    while (@a <=) BEGIN inserts into Nonclusteredtable VALUES (CAST (@a as NVARCHAR (2)) +replicate (' A ', 880))
       
SELECT @a = @a + 1 End       
       
       
--Query data SELECT * from Clusteredtable ORDER BY [C1] ASC SELECT * to nonclusteredtable order B Y [C1] ASC CREATE TABLE dbccresult (Pagefid NVARCHAR), Pagepid NVARCHAR (MB), Iamfid nvarcha   
R (Iampid NVARCHAR), ObjectID NVARCHAR, IndexID NVARCHAR, PartitionNumber NVARCHAR (200), PartitionID NVARCHAR (MB), Iam_chain_type NVARCHAR, PageType NVARCHAR (), Indexlevel NVARCHAR (m), Ne   
       
Xtpagefid NVARCHAR, Nextpagepid NVARCHAR, Prevpagefid NVARCHAR (200), Prevpagepid NVARCHAR) TRUNCATE TABLE [dbo].   
       
[Dbccresult] INSERT into Dbccresult EXEC (' DBCC IND (scandb,nonclusteredtable,-1) ') SELECT * from [dbo]. [Dbccresult]  Order BY [PageType] DESC DBCC TRACEON (3604,-1) go DBCC PAGE (scandb,1,89,3) go checkpoint DBCC Dropcleanbuffers DBCC freesystemcache (' All ') go-----------------------------------SET STATISTICS IO on Go--Clustered index lookup SELECT * from clusteredtable WHERE [c2]= ' 18aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa 
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa '   
       
   SET STATISTICS IO off go    
       
(1 rows affected) Table ' clusteredtable '.   
       
       
       
       
Scan count 1, logical read 4 times, physical read 2 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times. ----------------------------------------------------------------------------------------Checkpoint DBCC   
Dropcleanbuffers DBCC freesystemcache (' All ') go-----------------------------------SET STATISTICS IO in Go --index lookup, RID lookup, nested loop SELECT * from nonclusteredtable WHERE [c2]= ' 17aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa AaaaaaaaaaaaaaaAaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa   
       
       
       
Aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ' SET STATISTICS IO off go (1 rows affected) Table ' nonclusteredtable '. Scan count 1, logical read 5 times, physical read 1 times, read 0 times, LOB logic read 0 times, lob physical read 0 times, lob read 0 times.