【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页面




...全文
1214 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 2005微软官方权威参考手册　　 　　是Inside Microsoft SQL Server 2005系列书中的第一本，SQL Server类的顶尖之作 　　全球公认SQL Server 2005经典著作，囊括大量鲜为人知的技术内幕，大师智慧、专家经验尽览无余。　　　　 　　本系列图书中文版得到了微软总部SQL Server组专家的高度重视，同时也得到了微软中国上海SQL Server全球技术支持中心的高度关注。　　　　　 　　本书详细介绍了T-SQL的内部构造，包含了非常全面的编程参考。数据库开发人员和DBA可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言，以切合实际的方案来解决复杂的问题。本书涵盖了T-SQL程序设计的方方面面，如基于集合的编程技术、日期和时间相关的XML和CLR数据类型的使用、临时对象、T-SQL和CLR用户自定义函数、存储过程、触发器、事务和新的错误处理结构、应用并发模型支持并发用户、使用Service Broker来控制数据库应用程序中的异步处理等。 　 内容简介 本书是Inside Microsoft SQL Server 2005系列四本著作中的一本。它详细介绍了T-SQL的内部构造，包含了非常全面的编程参考。它提供了使用Transact-SQL（T-SQL）的专家级指导，T-SQL是用于SQL Server的最常见的也是功能最强大的编程语言。该书由Itzik Ben-Gan权威执笔，重点关注语言特性以及它们如何被SQL Server引擎解释和处理。 　　通过本书，你将深入了解T-SQL的高级用法，包括触发器、用户自定义函数、异常处理等。该书解释并比较了SQL Server 2000和SQL Server 2005在数据库开发相关问题上的解决方案，深入讨论了SQL Server 2005中新增的T-SQL编程特性，包含了大量的代码示例、表示例和逻辑难题以帮助数据库开发人员和管理员理解复杂的逻辑并掌握T-SQL。 　　本书适合于专业数据库开发者、BI开发者、DBA和以SQL Server作为后台数据库的一般应用程序开发者，读者可以通过书中的最佳实践、高级技巧和代码示例来掌握这门复杂的编程语言，以切合实际的方案来解决复杂的实际问题。 作者简介 Itzik Ben-Gan是Solid Quality Learning的导师和创始人。从1999年开始，他一直是Microsoft SQL Server MVP（最有价值专家），并在全世界已经开展过无数次T-SQL查询、T-SQL优化和编程方面的培训。Itzik是Microsoft SQL Server方面几本著作的作者。他在SQL ServerMagazine和MSDN上发表了许多文章和白皮书。Itzik被邀请在许多会议上做报告，包括TechEd、DevWeek、世界各地的各种SQL用户组、PASS、SQL server Magazine Connections和Solid Quality Learning的会议。 从1992年开始，Itzik就一直致力于涉及各种数据库和计算机系统相关技术的多个课题。除了帮助顾客处理紧迫的要求、修复问题、优化数据库、教学和担任顾问以外，他还帮助开发人员和数据库管理员转变关系/基于集合的理念，改善他们编写代码的性能和可维护性。Itzik擅长T-SQL查询、查询优化、编程和内部原理，此外他还精通其他的数据库领域。1999年，Itzik创立以色列SQL Server和OLAP用户组，一直管理至今。 目录 序 前言 致谢 引言 第1章 数据类型相关的问题，XML和CLR UDT 1.1 DATETIME数据类型 DATETIME的存储格式 时间处理 　　Datetime相关的查询问题 　1.2 与字符相关的问题 　　模式匹配 　　区分大小写(Case-Sensitive)的筛选器 　1.3 大型对象(Large Object，LOB) 　　MAX 说明符 　　BULK行集提供程序 　1.4 隐式转换(Implicit Conversion) 　　标量表达式 　　筛选表达式 　1.5 基于CLR的用户定义类型 　　UDT理论简介 　　开发UDT 　1.6 XML数据类型 　　关系数据库中的XML支持 　　什么时候应该使用XML代替关系表现形式？ 　　数据库中的XML序列化对象 　　使用开放架构（Open Schema）的XML 　　作为存储过程参数的XML数据类型 　　Xquery修改语句 　1.7 结论 第2章 临时表和表变量 　2.1 临时表 　　局部临时表 　　全局临时表 　2.2 表变量 　　限制条件 　　tempdb 　　范围和可见性 　　事务上下文 　　统计信息 　2.3 tempdb相关的注意事项 　2.4 表表达式 　2.5 比较临时对象 　2.6 综合练习——关系分区(Relational Division) 　2.7 结论 第3章 游标 　3.1 使用游标 　3.2 游标开销 　3.3 单独处理每一行 　3.4 按顺序访问 　　自定义聚合 　　连续聚合 　　最大并发会话 　　匹配问题 　3.5 结论 第4章 动态SQL 第5章 视图 第6章 用户定义函数 第7章 存储过程 第8章 触发器 第9章 事务 第10章 错误处理 第11章 Service Broker 附录A CLR程序指南 A.1 创建CLRUtilities数据库: SQL Server A.2 部署：Visual Studio A.3 部署和测试：Visual Studio 和 SQL Server 索引 中英文术语对照表
SQL SERVER 数据类型详解补充2
数据类型 类型 描 述 bit 整型 bit 数据类型是整型，其值只能是0、1或空值。这种数据类型用于存储只有两种可能值的数据，如Yes 或No、True 或Fa lse 、On 或Off int 整型 int 数据类型可以存储从- 231(-2147483648)到231 (2147483 647)之间的整数。存储到数据库的几乎所有数值型的数据都可以用这种数据类型。这种数据类型在数据库里占用4个字节 smallint 整型 smallint 数据类型可以存储从- 215(-32768)到215(32767)之间的整数。这种数据类型对存储一些常限定在
3天掌握SQL Server 2012 数据库
SQL Server是由Microsoft开发和推广的关系数据库管理系统（DBMS），Microsoft SQL Server 2012 是一个全面的数据库平台，使用集成的商业智能 (BI)工具提供了企业级的数据管理。Microsoft SQL Server 数据库引擎为关系型数据和结构化数据提供了更安全可靠的存储功能，使您可以构建和管理用于业务的高可用和高性能的数据应用程序 。 黄强老师针比较稳定且较新的SQL SERVER  2012版本演示数据库的安装使用，SQL(Structured Query Language)语言、T-SQL语言，数据库的增删改查、函数、存储过程、事务等重要知识点，让学员轻松入门并快速掌握关系型数据库！
SQL Server 2005中的XML数据类型提示
使用XML数据类型存储数据的技术。
【 SQL SERVER 2005页面存储之--表的数据行在页中的存储 】
                         SQL SERVER 2005页面存储之--表的数据行在页中的存储  第一篇来说说我们的表的数据行在页中的存储详解,也为接下来几篇关于特殊数据类型存储和索引的存储这篇讲得是没有聚集索引,没有非聚集索引,含有定长列和变长列的堆 /*------------------------------------------------
新技术前沿

6,129

社区成员

4,460

社区内容

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

加载中

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

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

+ 用AI写文章