【SQL SERVER 2005页面存储2之--特殊数据类型在页面中的存储】
本帖最后由 feixianxxx 于 2010-04-06 20:24:27 编辑 发帖排版部好，请进 博文地址：http://blog.csdn.net/feixianxxx/archive/2010/04/05/5452759.aspx
这一篇来讲讲特殊的数据类型在页面中的存储,这里分析2个特殊数据类型：LOB类型和SQL_VARIANT类型
LOB数据类型
当表中存在LOB类型数据(TEXT/NTEXT/IMAGE)时候：
默认的情况下(TEXT IN ROWS选项是关闭的)数据是不会存储在DATA页面上的.它是存储在属于自己的LOB页面上的,在数据页面只留下字节的指针；
在设置表选项来改变这个存储机制时候,他有可能会存储在DATA页面上。
那么我们来通过例子具体看下LOB类型数据的存储
/*----------------------------------
*auther:Poofly
*date:2010.3.14
*VERSION:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
*转载请注明出处
*更多精彩内容,请进http://blog.csdn.net/feixianxxx
------------------------------------*/
--建表(表源技术内幕)
if OBJECT_ID('Hastext') is not null
drop table Hastext
GO
create table Hastext
(
COL1 CHAR(3) NOT NULL,
COL2 VARCHAR(5) NOT NULL,
COL3 TEXT NOT NULL,--此处TEXT字段
COL4 VARCHAR(20) NOT NULL
)
--插入测试数据
INSERT Hastext
SELECT 'AAA','BBB',REPLICATE('X',250),'CCC'
--检查页面分布和类型
SELECT convert(char(7), object_name(object_id)) AS name,
partition_id, partition_number AS pnum, rows,
allocation_unit_id AS au_id, convert(char(17),type_desc) as page_type_desc,
total_pages AS pages
FROM sys.partitions p JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE object_id=object_id('dbo.Hastext');
--有IN_ROW_PAGE lOB_DATA 分配单元各有页，其中一页为IAM页。
--查找页面具体文件号和页面号
dbcc ind(tempdb,Hastext ,-1)
/*
PageFID PagePID iam_chain_type PageType
1 127 In-row data 10
1 126 In-row data 1 ---data page
1 174 LOB data 10
1 173 LOB data 3 ----LOB page
*/
--查看页面信息
dbcc traceon(3604) --此追踪可以显示页面输出结果
dbcc page(tempdb,1,126,1) --查看数据页的页面信息
/*
Slot 0, Offset 0x60, Length 40, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 40
Memory Dump @0x63D8C060
00000000: 30000700 41414104 00800300 15002580 ?...AAA.......%.
00000010: 28004242 420000e5 07000000 00ad0000 ?.BBB...........
00000020: 00010001 00434343 ?8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??....CCC PS:这里右边部分每一个小点表示一个字节
*/
--分析几个值：
我在这里分析下这个含有LOB数据类型的行的page读取方法 你可以对照我在上一偏文章<关于一般页面存储的研究>http://blog.csdn.net/feixianxxx/archive/2010/03/17/5390317.aspx的行结构进行对照 依次分解读取
30=>00110000 从左往右看第一个表示有变长列 第二个表示存在NULL位图
00=>00000000 未启用
0700=>0000000000001011 页位移量为 1+1+2+3(col1 char(3)),说明真正数据从第字节开始
414141=>010000010100000101000001 转成十进制 再转成ASICC码 值为A 即COL1 ‘AAA’
0400=>0000000000000100 一共列
08=>10000000 表里有列 最后位为 表示都不为NULL
0300=>0000000000000011 变长列为列 这里的TEXT列也算成变成列
1500=>0000000000010101 第一列变长列的终止位置
2580->1000000000100101 第二列变长列(TEXT)的终止位置
2800->0000000000101000=40 该列外为最后的可变列终止位置 正好就等于LENGTH 40
424242=>010000100100001001000010 转成十进制 再转成ASICC码 值为B 即COL1 ‘BBB’
0000e5 07000000 00ad0000 00010001 00==>这里的个字节是文本（TEXT）在这个DATA页面的指针
其中值ad00 0000是从这个指针留下来指向页面号的字节> Oxad00 0000
（Oxad00==>0000000010011101 转成十进制为 恰好是我们的LOB页面的页面号）,
后面的->00000000 00000001=1是LOB页面所在的文件号
再后面的->00000000 00000001=1是该条记录在LOB页面的Slot号.
434343==>转成十进制 再转成ASICC码 值为C 即COL1 ‘CCC’
我们开启表中的TEXT IN ROW 看看发生了什么
exec sp_tableoption Hastext,'text in row',500
--查看DATA页面
dbcc page(tempdb,1,126,1)
--发现结果跟不开启前一样那是因为要使在LOB页面上的数据转移到DATA页面必须更新文本
update Hastext
set COL3=REPLICATE('k',250)
--再次查看
dbcc page(tempdb,1,126,1)
/*
00000000: 30000700 41414104 00800300 15000f01 ?...AAA.........
00000010: 12014242 426b6b6b 6b6b6b6b 6b6b6b6b ?.BBBkkkkkkkkkkk
00000020: 6b6b6b6b 6b6b6b6b 6b6b6b6b 6b6b6b6b ?kkkkkkkkkkkkkkk
00000030: 6b6b6b6b 6b6b6b6b 6b6b6b6b 6b6b6b6b ?kkkkkkkkkkkkkkk
00000040: 6b6b6b6b 6b6b6b6b 6b6b6b6b 6b6b6b6b ?kkkkkkkkkkkkkkk
。。。
00000100: 6b6b6b6b 6b6b6b6b 6b6b6b6b 6b6b6b43 ?kkkkkkkkkkkkkkC
00000110: 4343?8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??8224 ??C
*/
我们可以清楚看到 6b6b 也就是KK都进入了DATA页面。。
--如果我们是改变该选项的大小上限呢
exec sp_tableoption Hastext,'text in row',50
--再次查看
dbcc page(tempdb,1,126,1)
/*
Slot 0, Offset 0x60, Length 48, DumpStyle BYTE
00000000: 30000700 41414104 00800300 15002d80 ?...AAA.......-.
00000010: 30004242 42040000 62010000 00366b00 ?.BBB...b....6k.
00000020: 00fa0000 00940000 00010000 00434343 ?............CCC
*/
变化:LOB数据消失在DATA页面
变化:Length 48 而不是 . 这里多出来的个字节是什么呢？我们来对比一下这行数据和上面一开始的数据,不同点在这：
0000e5 07000000 00ad0000 00010001 00==》字节的指针
040000 62010000 00366b00 00fa0000 00940000 00010000 00 ==》这是一个根结构
这里说明当我们修改选项上限大小,对于那些大小和上限不符合的(这里指大于上限)的LOB数据，在DATA页面存储的不是一个字节的指针，而是一个至少字节的根结构
ps:增加选项的上限的大小 效果是开启一样的 需要更新文本才能转移数据
--现在我们来关闭TEXT IN ROW 看看发生了什么
exec sp_tableoption Hastext,'text in row',0
--再次查看
dbcc page(tempdb,1,126,1)
--我们可以看到DATA页面的LOB数据又消失了,length 也回到了，也就是存字节的指针
结论:
LOB类型数据在数据页面的存储分种情况:
1.当表的'text in row'选项关闭的时候,我们的LOB数据在数据页面不会保存任何具体数据,只留下个字节指针;
2.当表的'text in row'选项开启的时候,如果行的LOB数据大小大于选项上限,会留下一个至少字节的指针(B-树的根结构).
如果行的LOB数据大小小于选项上限,在行大小能限制在的前提下,可以将LOB数据存储在数据页面,否则只能把LOB数据推到LOB页面
SQL_VARIANT数据类型
/*----------------------------------
*auther:poofly
*date:2010.3.14
*VERSION:
Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )
*转载请注明出处
*更多精彩内容,请进http://blog.csdn.net/feixianxxx
------------------------------------*/
sql_variant数据类型是一种特殊的数据类型, 它可以允许存储任何类型的数据，除了几下几种情况：(n)text、image、XML、UDT、通过MAX标识的变长数据类型、行版本以及cursor和table变量。显然CURSOR及表变量是不能定义为表的列类型。通过联机帮助我们可以看到sql_variant是MS为支持上层产品的半结构化数据而设计的。我们把这种类型应用概念类型的表中，比如为了便于扩展而增加一些事先不知道类型的列，那么我们就可以把这些列定义为sql_variant,这样一来概念表就转化为紧凑的真实表。
sql_variant 内部存储总是被认为变长的，其实SQLSERVER还是知道具体的类型的，因为存储结构的第一个字节总是表示该记录真实的基础数据类型，下面我们研究页面内容 时再细说这个字节。它的最大长度可以是 8016 个字节。实际基类型值的最大长度是 8,000 个字节。由于对这个类型的使用不是本文重点 所以不再阐述.
下面通过一个简单的测试来说明sql_variant类型的数据存储情况。
--建表
create table variant
(
col1 int,
col2 sql_variant
)
--插入数据
insert variant values(1,1)
insert variant values(2,100000000000)
insert variant values(3,'asasa')
insert variant values(4,CURRENT_TIMESTAMP)
go
--查看页面:只存在IN_ROW_DATA页面
SELECT convert(char(7), object_name(object_id)) AS name,
partition_id, partition_number AS pnum, rows,
allocation_unit_id AS au_id, convert(char(17),type_desc) as page_type_desc,
total_pages AS pages
FROM sys.partitions p JOIN sys.allocation_units a
ON p.partition_id = a.container_id
WHERE object_id=object_id('dbo.variant');
go
--取得文件号页面号
dbcc ind(poofly,'variant' ,-1)--5:42
go
--查看读取PAGE
dbcc traceon(3604) --此追踪可以显示页面输出结果
dbcc page(poofly,5,42,1)
--我们一条条数据来看
--第一条
/*
Slot 0, Offset 0x60, Length 21, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x6394C060
00000000: 30000800 01000000 02000001 00150038 ?..............8
00000010: 01010000 00? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?.....
*/
前面个字节我不再分析(每行也是一样的) 上一篇LOB存储已经解释过了。
1500->0000000000010101=5+16=21 表示第一列变长列终止的位置 表只有一个变长列所以正好等于记录长度
38->00111000=8+48=56 该字节表示你的SQL_VARIANT列的数据类型,为int类型
这里的数据类型的值就是sys.types视图中的system_type_id列的值
select name,system_type_id from sys.types
--类型很多列出几个需要的
/*
name system_type_id
int 56
datetime 61
numeric 108
varchar 167
*/
01->00000001 表示SQL_variant 版本的字节SQL2005/8里面总是
ps:由于是INT类型所以版本后面没有多余的字节(除了最后的真实数据),个别数据还是会有字节的看下面
010000 00->00...0001 转成十进制=1 插入的值
--第二条
/*
Slot 1, Offset 0x75, Length 28, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 28
Memory Dump @0x6394C075
00000000: 30000800 02000000 02000001 001c006c ?0..............l
00000010: 010c0001 00e87648 17000000 ??????????......vH....
*/
注意看这里的几点(特别是版本号后面Numberic类型对应的个字节)：
1c00->0000000000011100=28
6c->01101100 转成十进制就是再对应system_type_id 就是numberic 类型这里不用bigint类型的原因是这样存省空间
01->slq_variant版本号
0c00->00001100=12 00000000=0 第一个表示NUMBERIC的精度表示刻度 100000000000 不就是numberic(12,0)么?
01 00e87648 17000000->真实值
--第三条
/*
Slot 2, Offset 0x91, Length 28, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 28
Memory Dump @0x6394C091
00000000: 30000800 03000000 02000001 001c00a7 ?...............
00000010: 01401f24 d0000061 73617361 ? ?? ?? ?? ?? ?.@.$...asasa */
注意看这里的几点(特别是版本号后面varchar类型对应的个字节)：
1c00->0000000000011100=28
a7->10010111=167 再对应system_type_id 就是varchar 类型
01->slq_variant版本号
401f->0001111101000000=8000 字符串类型版本号后面带的个字节表示该类型的最大长度
24 d00000->00000000000000001101000000100100 排序规则ID
61 73617361->0110001=97='a' 1=01110011=115='s' 'asasa'长度五个字节.
--第三条
/*
Slot 3, Offset 0xad, Length 25, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 25
Memory Dump @0x6394C0AD
00000000: 30000800 04000000 02000001 0019003d ?..............=
00000010: 01754cdc 00399d00 00? ?? ?? ?? ?? ?? ?? ?? ??uL..9...
*/
1900->0000000000011001=25
3d->00111101=61 再对应system_type_id 就是datetime 类型
01->版本号
754cdc 00399d00 00->datetime类型个字节真实数据current_timestamp
ps:关于版本号后面各个数据类型哪些带字节哪些不带如下:
1.numeric/decimal:1个字节表示精度，个字节表示刻度
2.字符串:2个字节表示类型对应的最大长度,接下来的个字节表示排序规则的ID
3.binary/varbinary：个字节表示最长长度
4.其他数据类型无额外字节
呵呵，别磕，男儿膝下有黄金
[其他解释]
