【SQL SERVER 2005页面存储2之--特殊数据类型在页面中的存储】

feixianxxx 2010-04-05 09:42:48
加精
发帖排版部好，请进 博文地址：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页面




...全文
1216 96 打赏 收藏 转发到动态 举报
写回复
用AI写文章
96 条回复
切换为时间正序
请发表友善的回复…
发表回复
wulin505015 2010-04-13
  • 打赏
  • 举报
 回复
收藏。。。。。。。
season1668 2010-04-11
  • 打赏
  • 举报
 回复
学习了
linchb_ 2010-04-10
  • 打赏
  • 举报
 回复
studying
shaonew 2010-04-10
  • 打赏
  • 举报
 回复
kabnkana
ah23568 2010-04-09
  • 打赏
  • 举报
 回复
LOB类型和SQL_VARIANT类型，很有必要。
ps:关于版本号后面各个数据类型哪些带字节哪些不带如下:
1.numeric/decimal:1个字节表示精度，个字节表示刻度
2.字符串:2个字节表示类型对应的最大长度,接下来的个字节表示排序规则的ID
3.binary/varbinary：个字节表示最长长度
4.其他数据类型无额外字节
lcx87 2010-04-09
  • 打赏
  • 举报
 回复
我没有用过2005
lmc158 2010-04-09
  • 打赏
  • 举报
 回复
--建表
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.其他数据类型无额外字节
jiangseraph 2010-04-09
  • 打赏
  • 举报
 回复
占座学习
LoveFunny 2010-04-09
  • 打赏
  • 举报
 回复
无能为力！！！
mlhy20060406 2010-04-09
  • 打赏
  • 举报
 回复
学习了，厉害哦
voiceofghost 2010-04-09
  • 打赏
  • 举报
 回复
sql server2005 express. 学习中...
djp26357427 2010-04-09
  • 打赏
  • 举报
 回复
学习，呵呵
骷髅党小队长 2010-04-09
  • 打赏
  • 举报
 回复
学习了
bycz123456 2010-04-08
  • 打赏
  • 举报
 回复
很牛的说。。。。。。。。。
lhchn 2010-04-08
  • 打赏
  • 举报
 回复
的确厉害，分析的不错
kqwhdq 2010-04-08
  • 打赏
  • 举报
 回复
每天回帖即可获得10分可
黄_瓜 2010-04-08
  • 打赏
  • 举报
 回复
[Quote=引用 80 楼 perfectaction 的回复:]
我是初学者，还看不太懂
[/Quote]
mcken90 2010-04-08
  • 打赏
  • 举报
 回复
厉害，学习到了
nzperfect 2010-04-08
  • 打赏
  • 举报
 回复
我是初学者，还看不太懂
scofiledbo 2010-04-08
  • 打赏
  • 举报
 回复
我是初学者，还看不太懂
加载更多回复（72）
Microsoft SQL Server 2005技术内幕： T-SQ程序设计.pdf
该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案，深入讨论了SQL Server 2005中新增的T-SQL编程特性，包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解...
SQL SERVER 数据类型详解补充2
数据类型 类型 描 述 bit 整型 bit 数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或Fa lse 、On 或Off ...这种数据类型对存储一些常限定在
3天掌握SQL Server 2012 数据库
Microsoft SQL Server 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序 。 黄强老师针比较稳定且较新的SQL SERVER  2012版本演示...
SQL Server 2005中的XML数据类型提示
使用XML数据类型存储数据的技术。
【 SQL SERVER 2005页面存储之--表的数据行在页中的存储 】
SQL SERVER 2005页面存储之--表的数据行在页中的存储 第一篇来说说我们的表的数据行在页中的存储详解,也为接下来几篇关于特殊数据类型存储和索引的存储这篇讲得是没有聚集索引,没有非聚集索引,含有定长列和变长列的...
新技术前沿

6,129

社区成员

4,460

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 新技术前沿
社区管理员
  • 新技术前沿社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章