SQLServer 可变字符怎么设置长度？（如varchar）

今天突然想到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)）