SQL SERVER中Key Hash Value的作用（上）

測試環境：SQLSERVER2005 開發者版

真的不好意思，我做實驗的時候到最後還是沒有找到這個問題的答案

問題是這樣的：

當通過聚集索引查找和非聚集索引查找的時候，通過哈希碼來匹配，然後找到相應記錄的

既然通過哈希碼來匹配，那麼就需要一個hash bucket把所有索引頁面的所有key/value全部加載到hash bucket

既然要全部加載到hash bucket就需要讀取所有的索引頁

我的測試腳本，我使用SET STATISTICS IO ON來測試是否有讀取索引頁的情況，但是到最後還是找不到規律

--sql在聚集索引下如何找到哈希值的隨想   
       
USE master   
GO   
--新建數據庫IAMDB   
CREATE DATABASE SCANDB   
GO   
       
USE SCANDB   
GO   
       
       
       
--DROP TABLE clusteredtable   
--DROP TABLE nonclusteredtable   
       
       
--建立測試表   
CREATE TABLE clusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (900))   
GO   
CREATE TABLE nonclusteredtable(c1 INT IDENTITY(1,1), c2 VARCHAR (900))   
GO   
       
       
--建立索引   
CREATE CLUSTERED INDEX cix_clusteredtable ON clusteredtable([C2])   
GO   
CREATE  INDEX ix_nonclusteredtable ON nonclusteredtable([C2])   
GO   
       
       
--插入測試數據   
DECLARE @a INT;   
SELECT @a = 1;   
WHILE (@a <= 100)   
BEGIN
    INSERT INTO clusteredtable VALUES ( CAST(@a AS NVARCHAR(2))+replicate('a', 880))   
    SELECT @a = @a + 1   
END
       
       
DECLARE @a INT;   
SELECT @a = 1;   
WHILE (@a <= 100)   
BEGIN
    INSERT INTO nonclusteredtable VALUES ( CAST(@a AS NVARCHAR(2))+replicate('a', 880))   
    SELECT @a = @a + 1   
END
       
       
       
       
--查詢數據   
SELECT * FROM clusteredtable  ORDER BY [c1] ASC
SELECT * FROM nonclusteredtable  ORDER BY [c1] ASC
       
       
CREATE TABLE DBCCResult (   
PageFID NVARCHAR(200),   
PagePID NVARCHAR(200),   
IAMFID NVARCHAR(200),   
IAMPID NVARCHAR(200),   
ObjectID NVARCHAR(200),   
IndexID NVARCHAR(200),   
PartitionNumber NVARCHAR(200),   
PartitionID NVARCHAR(200),   
iam_chain_type NVARCHAR(200),   
PageType NVARCHAR(200),   
IndexLevel NVARCHAR(200),   
NextPageFID NVARCHAR(200),   
NextPagePID NVARCHAR(200),   
PrevPageFID NVARCHAR(200),   
PrevPagePID NVARCHAR(200)   
)   
       
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   
--聚集索引查找   
SELECT * FROM clusteredtable WHERE [c2]='18aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
SET STATISTICS IO OFF
GO   
       
       
       
(1 行受影響)   
表 'clusteredtable'。掃描計數 1，邏輯讀取 4 次，物理讀取 2 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。   
       
       
       
       
----------------------------------------------------------------------------------------   
checkpoint
DBCC DROPCLEANBUFFERS   
DBCC freesystemcache('all')   
GO   
-----------------------------------   
SET STATISTICS IO ON
GO   
--索引查找  、RID查找 、嵌套循環   
SELECT * FROM nonclusteredtable WHERE [c2]='17aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
SET STATISTICS IO OFF
GO   
       
       
       
(1 行受影響)   
表 'nonclusteredtable'。掃描計數 1，邏輯讀取 5 次，物理讀取 1 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。