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 次。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索测试
， table
， 索引
， bucket
， 哈希
， create
Hash索引
hash key value、valueforkeypath作用、hash value、plan hash value、passwordhashvalue，以便于您获取更多的相关知识。