Functions of KeyHashValue in SQLSERVER (I)

The role of KeyHashValue in SQLSERVER (I) the role of KeyHashValue in SQLSERVER (II) the title of the original article is: SQLSERVER how to find the hash value under the index and now I want to know the role of KeyHashValue, so I changed the title ~ Test environment: SQLSERVER2005 Developer Edition is really embarrassing.

The role of KeyHashValue in SQLSERVER (I) the role of KeyHashValue in SQLSERVER (II) the title of the original article is: SQLSERVER how to find the hash value under the index and now I want to know the role of KeyHashValue, so I changed the title ~ Test environment: SQLSERVER2005 Developer Edition is really embarrassing.

Functions of KeyHashValue in SQLSERVER (I)

Role of KeyHashValue in SQLSERVER (below)

The title of the original article is: how to find the hash value under the index of SQLSERVER

Now that we know the role of KeyHashValue, we changed the title ~

Test environment: SQLSERVER2005 Developer Edition

Sorry, I still did not find the answer to this question at the end of my experiment.

The problem is as follows:

When you search for clustered index and non-clustered index, you can use the hash code to match and then find

Since the hash code is used for matching, a hash bucket is required to load all the keys/Values on all index pages to the hash bucket.

To load all the data to a hash bucket, you must read all the index pages.

In my test script, I use SET STATISTICS IO ON to test whether the index page is read, but the rule still cannot be found at the end.

Master SCANDB SCANDBclusteredtable (c1 int identity (900), c2 VARCHAR (900) nonclusteredtable (c1 int identity (), c2 VARCHAR () cix_clusteredtable) ix_nonclusteredtable );;) clusteredtable (, 880);) nonclusteredtable (, 880) clusteredtable nonclusteredtable DBCCResult (59 PageFID NVARCHAR (200), 60 PagePID NVARCHAR (200), 61 iamfid nvarchar (200 ), 62 iampid nvarchar (200), 63 ObjectID NVARCHAR (200) , 64 IndexID NVARCHAR (200), 65 PartitionNumber NVARCHAR (200), 66 PartitionID NVARCHAR (200), 67 iam_chain_type NVARCHAR (200), 68 PageType NVARCHAR (200 ), 69 IndexLevel NVARCHAR (200), 70 NextPageFID NVARCHAR (200), 71 NextPagePID NVARCHAR (200), 72 PrevPageFID NVARCHAR (200), 73 PrevPagePID NVARCHAR (200) 74 ). DBCCResult ). TRACEON (3604,-1) PAGE (SCANDB, 3) DROPCLEANBUFFERS) IO clusteredtable IO (1 row affected) . 1 scan count, 4 logical reads, 2 Physical reads, 0 pre-reads, 0 lob logical reads, 0 lob physical reads, and 0 lob pre-reads. DROPCLEANBUFFERS) IO nonclusteredtable IO (1 row affected ). 1 scan count, 5 logical reads, 1 physical read, 0 preread, 0 lob logical reads, 0 lob physical reads, and 0 lob preread.