HoneyHoney

SQL Server有IN_ROW，ROW_OVERFLOW和LOB 3种page。

首先，来看看ROW_OVERFLOW是怎么存储的吧。

首先创建一个Table，插入一下数据。ID和Col1会存储在第一个page上，即IN_ROW page。

IF OBJECT_ID('RowOverflow') IS NOT NULL
    DROP TABLE RowOverflow;
GO
create table dbo.RowOverflow 
( 
    ID int not null, 
    Col1 varchar(8000) null, 
    Col2 varchar(8000) null 
);

insert into dbo.RowOverflow(ID, Col1, Col2) 
values (1,replicate('a',8000),replicate('b',8000));

验证一下表的数据页

SELECT allocated_page_file_id as PageFID, allocated_page_page_id as PagePID,
       object_id as ObjectID, partition_id AS PartitionID,
       allocation_unit_type_desc as AU_type, page_type as PageType
FROM sys.dm_db_database_page_allocations(db_id('test_db'), object_id('RowOverflow'),
                                          null, null, 'DETAILED');
GO

结果如图。一共有4个page。两个IAM，一个数据页，另外一个LOB或者Overflow page。

执行DBCC PAGE，可以看到第一页的存储情况。

DBCC TRACEON (3604); 
GO 
DBCC PAGE ('test_db', 1, 610871, 3); 
GO

数据的最后一共有24bytes，这些都是字节交换的，所以需要倒着读。

1. 02000000 01000000 29000000 401f0000 是metadata attribute。
2. 31520900 是page number
3. 0001 是file number
4. 0000 是slot number

在Kalen写的SQL SERVER 2012 internals里面提到了metadata attribute。

The first 16 bytes of a row-overflow pointer

Bytes	Hex value	Decimal value	Meaning
0	0x02	2	Type of special field: 1 = LOB2 = overflow
1–2	0x0000	0	Level in the B-tree (always 0 for overflow)
3	0x00	0	Unused
4–7	0x00000001	1	Sequence: a value used by optimistic concurrency control for cursors that increases every time a LOB or overflow column is updated
8–11	0x00007fc3	32707	Timestamp: a random value used by DBCC CHECKTABLE that remains unchanged during the lifetime of each LOB or overflow column
12–15	0x00000834	2100	Length

31520900应该读作0X00095231，换做十进制的610865。


继续往下看，你可以看到Col2的RowId记录的page number。这个也印证了我们从dm_db_database_page_allocations里看到的情况。
来看看ROW_OVERFLOW page的情况。就可以看到Col2的数据了。

DBCC PAGE ('test_db', 1, 610871, 3); 
GO

参考

1. http://aboutsqlserver.com/2013/11/05/sql-server-storage-engine-lob-storage/
2. https://www.microsoftpressstore.com/articles/article.aspx?p=2225060