查询表的分配单元数据

分配单元是堆或 B 树内用于根据页类型管理数据的页集合。下表列出了用于管理表和索引中的数据的分配单元类型。

分配单元类型	用途	页类型
IN_ROW_DATA	包含除大型对象 (LOB) 数据以外的所有数据的数据行或索引行。	Data/Image
LOB_DATA	以下列一种或多种数据类型存储的大型对象数据：text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max) 或 CLR 用户定义类型 (CLR UDT)。	Text/Image
ROW_OVERFLOW_DATA	存储在超过 8,060 字节行大小限制的 varchar、nvarchar、varbinary 或 sql_variant 列中的可变长度数据。	Text/Image

有关页类型的详细信息，请参阅页和区。

首先仍是构建一个测试环境，ClassA和ClassB两张表，各有10W条记录，ClassA有一个聚集索引（主键）和一个名为IDX_CreateDate的非聚集索引，ClassB类似，但比表A多一个非聚集索引。

USE tempdb
GO
SET NOCOUNT ON
IF OBJECT_ID(N'ClassA', N'U') IS NOT NULL
    DROP TABLE ClassA
IF OBJECT_ID(N'ClassB', N'U') IS NOT NULL
    DROP TABLE ClassB
CREATE TABLE ClassA(ID INT PRIMARY KEY, Name VARCHAR(16), CreateDate DATETIME)
CREATE INDEX IDX_CreateDate ON ClassA(CreateDate)
GO
CREATE TABLE ClassB(ID INT PRIMARY KEY, Name VARCHAR(16), CreateDate DATETIME, AID INT, Status INT)
CREATE INDEX IDX_CreateDate ON ClassB(CreateDate)
CREATE INDEX IDX_AID ON ClassB(AID)
GO

DECLARE @ID INT
SET @ID = 1
WHILE @ID <= 100000
BEGIN
    INSERT INTO ClassA VALUES(@ID, 'fx', GETDATE())
    SET @ID = @ID + 1
END

SET @ID = 1
WHILE @ID <= 100000
BEGIN
    INSERT INTO ClassB VALUES(@ID, 'fx', GETDATE(), @ID % 20, @ID % 20)
    SET @ID = @ID + 1
END

查询表的总记录数；

SELECT 'ClassA' AS ClassA, COUNT(1) AS Count FROM ClassA
SELECT 'ClassB' AS ClassA, count(1) AS Count FROM ClassB

确保测试数据插入成功；

分配单元数据的查询脚本如下：

SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_type, au.data_pages, partition_number
FROM sys.allocation_units AS au
    JOIN sys.partitions AS p ON au.container_id = p.partition_id
    JOIN sys.objects AS o ON p.object_id = o.object_id
    JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id
WHERE o.name = N'ClassA' OR o.name = N'ClassB' ORDER BY o.name, p.index_id

查询结果显示表A有两行，对应着主键上的聚集索引（index_id=1），和CreateDate上的非聚集索引（index_id=2），表B除此之外多一行：列AID上面的非聚集索引。

但是为何同行的数据，表A的聚集索引ID只有335页数据，而表B却有433页，多出来的98页从何而来？

这里就涉及到表和索引数据结构体系结构了，在聚集索引中，叶节点包含基础表的数据页。

因为表B比表A多了两个类型为INT的字段，即8个长度，所以每一页存储的数据行数就少，为证实这一点，使用下列命令查询索引页的明细；

DBCC IND(tempdb, ClassA, 1)
DBCC IND(tempdb, ClassB, 1)

我们随意从两张表的聚集索引页中各自拿出来一页出来进行分析；

DBCC TRACEON (3604)
GO
DBCC PAGE (tempdb, 1, 180, 1)
DBCC PAGE (tempdb, 1, 488, 1)

页180（表B）有299行（m_slotCnt），每行长度为25；

页488（表B）有231行（m_slotCnt），每行长度为33；

回过头来看看两张表的非聚集索引（AID），他们页数一样，因为非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

在此例中，由于两张表都是聚集表，则行定位器是行的聚集索引键，自身的索引键为INT类型，占4个字节，定位符也是INT，同样4个字节）；如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。即此非聚集索引页中的每行长度为12个字节。

重复上述步骤，随意找出一张非聚集索引页予以证实。

DBCC IND(tempdb, ClassA, 3)