今天突然想到nvarchar类型,联想到数据分配单元的3中类型:IN_ROW_DATA、LOB_DATA和ROW_OVERFLOW_DATA
官网再次查看了一下文档:表组织和索引组织(https://msdn.microsoft.com/zh-cn/library/ms189051(v=sql.105).aspx)
其中看到下面图中的这句话:
IN_ROW_DATA数据行超过8KB的行大小限制,SQLServer将把最宽的列从该行移动到ROW_OVERFLOW_DATA分配单元中的页。原始页上将维护一个指向此行外数据的24字节指针。
问:到底是将这一行中的列值移到ROW_OVERFLOW_DATA,还是将表中的整列到ROW_OVERFLOW_DATA??
我想也是将行中的列值移过去,但是还是测试看看吧,也顺便说明几个DBCC的使用。
测试:
--创建测试表
-- DROP TABLE TabTest
CREATETABLETabTest(IDINT,COLCHAR(100),NAMEVARCHAR(8000))
--插入一行数据
INSERTINTOTabTestSELECT1,'A','AAAAA'
--打开跟踪并查看数据页结构
DBCCTRACEON(3604,-1)
DBCCIND(Temp,TabTest,1)
可以看到iam_chain_type='In-rowdata'说明数据是保存在IN_ROW_DATA分配单元的,根据PageFID和PagePID查看数据页内部情况。
DBCCPAGE(Temp,1,16051,3)
页头不说明,只看数据内容,把关注点也标明了,如下图:
当前数据页内,只有一行数据,数据值也都显示出来了。
以 id=4为例,Offset0x4 Length 4,Offset 0x4十六进制换成十进制为4,表示跳过前4个字节。
第一行 00000000:30006c000100000041202020 20202020 †0.l.....A 中,前4个十六进制的字节跳过,
其中2位十六进制转换成字符后为1个(参考下图)。id=1在数据页中的存储表示为01000000,同理可计算其他列。
--再插入一行数据,该行数据的存储量已经大于一页
INSERTINTOTabTestSELECT2,'B',REPLICATE('B',8000)
DBCCIND(Temp,TabTest,1)
数据存储单元中有多了一种类型'Row-overflowdata',如开始的问题,是将这一行中的列值移到ROW_OVERFLOW_DATA,还是将表中的整列到ROW_OVERFLOW_DATA??用DBCCPAGE查看数据页16051和16053.
DBCCPAGE(Temp,1,16051,3)
原来 id= 1 是数据都还在,并没有移到ROW_OVERFLOW_DATA。
而id= 2的行中,字段name并没有存储在该数据页中,其中页中记录的信息如下:
NAME= [BLOB Inline Root]Slot 1 Column 3 Offset 0x73 Length 24 Length (physical) 24
name的值指向了[BLOBInline Root],并且占用长度为24字节(Length24)。
往下看,可以看到指针的指向位置。
数据页及位置:RowId= (1:16053:0) TimeStamp = 1390739456
DBCCPAGE(Temp,1,16053,3)
在这个数据页(1:16053)中的第一个槽(Slot0)中,都存储了id= 2中字段name的值。
也就证明了起初的答案:
可变字符类型中,如果某字段插入的值超过了数据页的长度,该行的字段值将存放到ROW_OVERFLOW_DATA中,并且在原数据页中保留了长度为24字节的指针。
此时查看该表数据的IO情况:
SETSTATISTICSIOON
select*fromTabTest
(2行受影响)
表'TabTest'。扫描计数1,逻辑读取1次,物理读取0次,预读0次,lob逻辑读取1次,lob物理读取0次,lob预读0次。
表中进行了一次lob逻辑读取,如果每一行都溢出,那可能是多一倍的IO!
那对于一些字符类型的字段,尤其是可变字符的字段,以varchar为例:varchar(256), varchar(8000),varchar(max)怎么设置?
首先确定几个事情:
数据页存储数据的大小是固定的;
可变字符超过了单页存储大小则移到ROW_OVERFLOW_DATA中;
text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max)存储在LOB_DATA中(可自行测试);
怎么设置大小(个人理解):
1.表中只有1个可变字段类型的,并且该字段很少有值,或者值都比较小,可以限定字符长度 (如 varchar(256),长度依情况而定)
2.表中只有1个可变字段类型的,并且该字段很少有值,或者值都比较小,偶尔出现几千个字符的长度,可设置长度大一些 (如 varcahr(8000),长度依情况而定)
3.表中只有1个可变字段类型的,长度不定,字段长度成百上千的居多,直接存储在LOB_DATA类型的数据页中 (如 varcahr(max))
4.表中只有多个可变字段类型的,如果可变字符类型的长度远超过了其他字段类型的总和,建议存储在LOB_DATA类型的数据页中 (如 varcahr(max))
5.表中只有多个可变字段类型的,如果该字段很少有值,或者值都比较小,可以限定字符长度(如varchar(256),长度依情况而定),否则建议存储在LOB_DATA类型的数据页中(如varcahr(max))
6.如果表是专门存储可变字符用的(这种设计较好),如一个表中,除了相关主键外键,就只存储可变字符,不管怎么变,都存储在最大限制中(如varcahr(8000))