SQLSERVER中KeyHashValue的作用（下）-mysql教程-PHP中文网

1 DBCC TRACEON(3604,-1) 2 GO 3 DBCC PAGE(testhashkey,1,126,3) 4 GO

1 USE master 2 GO 3 CREATE DATABASE testhashkey 4 GO 5 6 USE testhashkey 7 GO 8 9 -------------------------------------------- 10 --测试聚集索引 11 CREATE TABLE testcluster 12 ( 13 a NVARCHAR(3800) NOT NULL , 14 b INT NOT NULL 15 ) 16 GO 17 18 --这里一定要是唯一的 19 CREATE UNIQUE CLUSTERED INDEX ucl ON testcluster(b) 20 GO 21 22 INSERT testcluster 23 SELECT CAST(11 AS VARCHAR(10))+replicate('a', 3500),1 UNION ALL 24 SELECT CAST(22 AS VARCHAR(10))+replicate('a', 3500),2

1 CREATE TABLE DBCCResult ( 2 PageFID NVARCHAR(200), 3 PagePID NVARCHAR(200), 4 IAMFID NVARCHAR(200), 5 IAMPID NVARCHAR(200), 6 ObjectID NVARCHAR(200), 7 IndexID NVARCHAR(200), 8 PartitionNumber NVARCHAR(200), 9 PartitionID NVARCHAR(200), 10 iam_chain_type NVARCHAR(200), 11 PageType NVARCHAR(200), 12 IndexLevel NVARCHAR(200), 13 NextPageFID NVARCHAR(200), 14 NextPagePID NVARCHAR(200), 15 PrevPageFID NVARCHAR(200), 16 PrevPagePID NVARCHAR(200) 17 ) 18 19 TRUNCATE TABLE [dbo].[DBCCResult] 20 21 INSERT INTO DBCCResult EXEC ('DBCC IND(testhashkey,testcluster,-1) ') 22 23 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC

1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 2 GO 3 BEGIN TRANSACTION 4 SELECT b FROM [dbo].[testcluster] WHERE [b]=1 5 --COMMIT TRAN 6 7 SELECT resource_type , 8 resource_database_id , 9 resource_description , 10 resource_associated_entity_id , 11 request_mode , 12 request_type , 13 request_status 14 FROM sys.dm_tran_locks 15 WHERE [resource_database_id]=DB_ID('testhashkey') 16 ORDER BY [resource_type]

1 USE master 2 GO 3 CREATE DATABASE practice 4 GO 5 6 USE practice 7 GO 8 9 --只有聚集索引 10 CREATE TABLE Department( 11 DepartmentID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 12 Name NVARCHAR(200) NOT NULL, 13 GroupName NVARCHAR(200) NOT NULL, 14 Company NVARCHAR(300), 15 ModifiedDate datetime NOT NULL DEFAULT (getdate()) 16 ) 17 18 INSERT INTO Department(name,[Company],groupname) VALUES('销售部','中国你好有限公司XX分公司','销售组') 19 GO 100000 20 21 CREATE TABLE DBCCResult ( 22 PageFID NVARCHAR(200), 23 PagePID NVARCHAR(200), 24 IAMFID NVARCHAR(200), 25 IAMPID NVARCHAR(200), 26 ObjectID NVARCHAR(200), 27 IndexID NVARCHAR(200), 28 PartitionNumber NVARCHAR(200), 29 PartitionID NVARCHAR(200), 30 iam_chain_type NVARCHAR(200), 31 PageType NVARCHAR(200), 32 IndexLevel NVARCHAR(200), 33 NextPageFID NVARCHAR(200), 34 NextPagePID NVARCHAR(200), 35 PrevPageFID NVARCHAR(200), 36 PrevPagePID NVARCHAR(200) 37 ) 38 39 TRUNCATE TABLE [dbo].[DBCCResult] 40 41 INSERT INTO DBCCResult EXEC ('DBCC IND(practice,Department,-1) ') 42 43 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 44 45 DBCC TRACEON(3604,-1) 46 GO 47 DBCC PAGE(practice,1,114,3) 48 GO 49 50 select %%lockres%% AS '数据页的keyhashvalue' from Department 51 52 53 54 55 56 57 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 58 GO 59 BEGIN TRANSACTION 60 SELECT DepartmentID FROM Department WHERE DepartmentID BETWEEN 0 AND 122 61 --COMMIT TRAN 62 63 SELECT resource_type , 64 resource_database_id , 65 resource_description , 66 resource_associated_entity_id , 67 request_mode , 68 request_type , 69 request_status 70 FROM sys.dm_tran_locks 71 WHERE [resource_database_id]=DB_ID('practice') 72 AND [resource_description] LIKE '%b03b%' 73 ORDER BY [resource_type]

1 --测试非聚集索引 2 USE [testhashkey] 3 GO 4 CREATE TABLE testnoncluster 5 ( 6 a NVARCHAR(3800) NOT NULL , 7 b INT NOT NULL 8 ) 9 GO 10 11 CREATE UNIQUE NONCLUSTERED INDEX ucil ON testnoncluster(b) 12 GO 13 14 INSERT testnoncluster 15 SELECT CAST(11 AS VARCHAR(10))+replicate('a', 3500),1 UNION ALL 16 SELECT CAST(22 AS VARCHAR(10))+replicate('a', 3500),2 17 18 19 SELECT * FROM [dbo].[testnoncluster]

1 TRUNCATE TABLE [dbo].[DBCCResult] 2 3 INSERT INTO DBCCResult EXEC ('DBCC IND(testhashkey,testnoncluster,-1) ') 4 5 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 6 7 DBCC TRACEON(3604,-1) 8 GO 9 DBCC PAGE(testhashkey,1,77,3) 10 GO

1 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 2 GO 3 BEGIN TRANSACTION 4 SELECT b FROM [dbo].[testnoncluster] WHERE [b]=1 5 --COMMIT TRAN 6 7 SELECT resource_type , 8 resource_database_id , 9 resource_description , 10 resource_associated_entity_id , 11 request_mode , 12 request_type , 13 request_status 14 FROM sys.dm_tran_locks 15 WHERE [resource_database_id]=DB_ID('testhashkey') 16 ORDER BY [resource_type]

1 --测试堆表和非聚集索引表的数据页也有keyhashvalue 2 USE [testhashkey] 3 GO 4 5 6 CREATE TABLE testnoncluster2 7 ( 8 a NVARCHAR(3800) NOT NULL , 9 b INT NOT NULL, 10 c INT NOT NULL 11 ) 12 GO 13 14 15 16 CREATE UNIQUE NONCLUSTERED INDEX ucil ON testnoncluster2(b) 17 GO 18 19 INSERT testnoncluster2 20 SELECT CAST(11 AS VARCHAR(10))+replicate('a', 3500),1,1 UNION ALL 21 SELECT CAST(22 AS VARCHAR(10))+replicate('a', 3500),2,2 22 23 24 25 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE 26 GO 27 BEGIN TRANSACTION 28 DELETE FROM [dbo].[testnoncluster2] WHERE [c]=1 29 --ROLLBACK TRAN 30 31 SELECT resource_type , 32 resource_database_id , 33 resource_description , 34 resource_associated_entity_id , 35 request_mode , 36 request_type , 37 request_status 38 FROM sys.dm_tran_locks 39 WHERE [resource_database_id]=DB_ID('testhashkey') 40 ORDER BY [resource_type] 41 42 43 44 45 select %%lockres%% AS '数据页的keyhashvalue' from testnoncluster2

1 --测试堆表和非聚集索引表的数据页也有keyhashvalue 2 USE [testhashkey] 3 GO 4 5 6 7 CREATE TABLE testnoncluster2 8 ( 9 a NVARCHAR(3800) NOT NULL , 10 b INT NOT NULL, 11 c INT NOT NULL 12 ) 13 GO 14 15 16 17 CREATE UNIQUE NONCLUSTERED INDEX ucil ON testnoncluster2(b) 18 GO 19 20 INSERT testnoncluster2 21 SELECT CAST(11 AS VARCHAR(10))+replicate('a', 3500),1,1 UNION ALL 22 SELECT CAST(22 AS VARCHAR(10))+replicate('a', 3500),2,2 23 24 25 26 27 28 TRUNCATE TABLE [dbo].[DBCCResult] 29 30 INSERT INTO DBCCResult EXEC ('DBCC IND(testhashkey,testnoncluster2,-1) ') 31 32 SELECT * FROM [dbo].[DBCCResult] ORDER BY [PageType] DESC 33 34 DBCC TRACEON(3604,-1) 35 GO 36 DBCC PAGE(testhashkey,1,165,3) 37 GO

PHP速学视频免费教程(入门到精通)

PHP怎么学习？PHP怎么入门？PHP在哪学？PHP怎么学才快？不用担心，这里为大家提供了PHP速学教程(入门到精通)，有需要的小伙伴保存下载就能学习啦！

SQLSERVER中KeyHashValue的作用（下）

PHP速学视频免费教程(入门到精通)

相关文章

最新文章

￥15

￥799

￥79

￥119

￥0

￥168