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

--创建测试数据库
CREATE DATABASE Ixdata
GO
USE [Ixdata]
GO
---创建测试表
CREATE TABLE Orders
(ID INT PRIMARY KEY IDENTITY(1,1),
NAME CHAR(80)NOT NULL,
IDATE DATETIME NOT NULL DEFAULT(GETDATE())
);
GO
---插入1000条测试数据
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Orders(NAME)VALUES('商品'+CONVERT(NVARCHAR(20),@ID))
SET @ID=@ID+1 
END
GO
SELECT * FROM Orders 
GO

---显示跟踪标志的状态
DBCC TRACESTATUS

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

/*
1：显示所有分页的信息，包括IAM分页,数据分页,所有存在的LOB分页和行溢出页,索引分页
-1： 显示所有IAM、数据分页、及指定对象上全部索引的索引分页.
-2： 显示指定对象的所有IAM分页
0:显示所有IAM、数据分页.
*/

SELECT DISTINCT so.name, so.object_id,i.name AS index_name,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
LEFT JOIN sys.indexes  i ON so.object_id=i.object_id AND sp.index_id=i.index_id
WHERE so.object_id = object_id('orders') 

---DBCC page的格式为(数据库，文件id,页号，显示)
DBCC page(Ixdata,1,89,3)

--插入20万条记录分析索引结构 
DECLARE @ID INT=1
WHILE(@ID<=200000)
BEGIN
INSERT INTO Orders(NAME)VALUES('商品'+CONVERT(NVARCHAR(20),@ID))
SET @ID=@ID+1 
END




CREATE TABLE Page
(
  PageFID         TINYINT, 
  PagePID         INT,   
  IAMFID          TINYINT, 
  IAMPID          INT, 
  ObjectID        INT,
  IndexID         TINYINT,
  PartitionNumber TINYINT,
  PartitionID     BIGINT,
  iam_chain_type  VARCHAR(30),    
  PageType        TINYINT, 
  IndexLevel      TINYINT,
  NextPageFID     TINYINT,
  NextPagePID     INT,
  PrevPageFID     TINYINT,
  PrevPagePID     INT 
);
GO
INSERT INTO Page EXEC('DBCC IND(Ixdata,Orders,-1)')

---查询索引页
SELECT  [PageFID]
      ,[PagePID]
      ,[IAMFID]
      ,[IAMPID]
      ,[ObjectID]
      ,[IndexID]
      ,[PartitionNumber]
      ,[PartitionID]
      ,[iam_chain_type]
      ,[PageType]
      ,[IndexLevel]
      ,[NextPageFID]
      ,[NextPagePID]
      ,[PrevPageFID]
      ,[PrevPagePID]
  FROM [Ixdata].[dbo].[Page]
  WHERE PageType=2
  go
 select so.name, so.object_id, sp.index_id, 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('orders') 

---DBCC page的格式为(数据库，文件id,页号，显示)
DBCC page(Ixdata,1,89,3)

---创建测试表
CREATE TABLE Orderslob
(ID INT PRIMARY KEY IDENTITY(1,1),
NAME CHAR(80)NOT NULL,
Product NVARCHAR(MAX) NOT NULL,
IDATE DATETIME NOT NULL DEFAULT(GETDATE())
);
GO
---插入1000条测试数据
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Orderslob(NAME,Product)VALUES(CONVERT(NVARCHAR(20),@ID)+'商品',REPLICATE(@ID,2))
SET @ID=@ID+1 
END
--REPLICATE(@ID,200)
GO

DBCC IND(Ixdata,Orderslob,1)

--查看2719数据页的信息
DBCC page(Ixdata,1,2719,1)