SQL Server 深入解析索引存储(非聚集索引)

CREATE TABLE Torder
(ID INT IDENTITY(1,1) NOT NULL,
NAME CHAR(100) NOT NULL,
pro VARCHAR(8000) NULL,
Statu INT NOT NULL,
IDATE DATETIME DEFAULT(GETDATE())
)
GO
---插入1000条测试数据
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Torder(NAME,pro,Statu)VALUES('商品'+CONVERT(CHAR(20),@ID),REPLICATE(1,8000),LEFT(@ID,1))
SET @ID=@ID+1 
END
GO
---创建非聚集索引
CREATE INDEX IX_Torder ON Torder
(NAME,Statu
)
INCLUDE(IDATE)


SELECT DISTINCT so.name, so.object_id,sp.index_id,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_page
FROM sys.objects so
INNER JOIN sys.partitions sp ON so.object_id = sp.object_id
INNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_id
INNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_id
WHERE so.object_id = object_id('Torder') 

---开启跟踪标志
DBCC TRACEON(3604,2588)
--DBCC TRACEOFF(3604,2588)
---获取对象的数据页，结构：数据库、对象、显示
DBCC IND(Ixdata,Torder,-1)

DBCC page(Ixdata,1,1281,3)

DBCC page(Ixdata,1,360,3)

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN  
SELECT [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE NAME='商品153'

--COMMIT 

另开一个窗口
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
 WHERE c.[dbid]=DB_ID('Ixdata') AND a.[request_session_id]=58  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO        
BEGIN TRAN  
 SELECT 
       [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE NAME='商品150'                                                                                     
  

update Torder
set statu=100
where id=1

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO        
BEGIN TRAN  
 SELECT 
       [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE [Statu]=100      

ALTER TABLE dbo.Torder ADD CONSTRAINT
    PK_Torder PRIMARY KEY CLUSTERED 
    (
    ID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


SELECT DISTINCT so.name, so.object_id,sp.index_id,internals.type_desc,internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page,  first_page, root_page
FROM sys.objects so
INNER JOIN sys.partitions sp ON so.object_id = sp.object_id
INNER JOIN sys.allocation_units sa ON sa.container_id = sp.hobt_id
INNER JOIN sys.system_internals_allocation_units internals ON internals.container_id = sa.container_id
WHERE so.object_id = object_id('Torder') 

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN  
SELECT [ID]
      ,[NAME]
      ,[pro]
      ,[Statu]
      ,[IDATE]
  FROM [Ixdata].[dbo].[Torder]
  WHERE NAME='商品152'


在另一个窗口打开
SELECT
[request_session_id],
c.[program_name],
DB_NAME(c.[dbid]) AS dbname,
[resource_type],
[request_status],
[request_mode],
[resource_description],OBJECT_NAME(p.[object_id]) AS objectname,
p.[index_id]
FROM sys.[dm_tran_locks] AS a LEFT JOIN sys.[partitions] AS p
ON a.[resource_associated_entity_id]=p.[hobt_id]
LEFT JOIN sys.[sysprocesses] AS c ON a.[request_session_id]=c.[spid]
 WHERE c.[dbid]=DB_ID('Ixdata') AND a.[request_session_id]=58  ----要查询申请锁的数据库
ORDER BY [request_session_id],[resource_type]

DBCC SHOW_STATISTICS ("dbo.Torder", IX_Torder);