SQLSERVER中数据行所占用的最小空间

数据行所占的最小空间是多少呢？ 先来看看下面这张经典数据行结构图（引自Inside Sql Server） 因为可变长度类型的列会有额外的空间开销，所以不考虑可变长度的字段。 现在计算一下除去实际数据后每个数据行所需要占用的空间： 状态位A占一个字节，状态位B占

            数据行所占的最小空间是多少呢？

  先来看看下面这张经典数据行结构图（引自Inside Sql Server）



     因为可变长度类型的列会有额外的空间开销，所以不考虑可变长度的字段。   现在计算一下除去实际数据后每个数据行所需要占用的空间：

    状态位A占一个字节，状态位B占1个字节，用于标识固定字段长度的占2个字节，标识固定列数量的占去2个字节，NULL位图至少占用1个字节。
这样算下来：1+1+2+2+1=7。 也就是在不考虑实际数据的情况下一个数据行最少要占用7个字节。如果实际数据长度为1，那么每一行所占用的空间就为8个字节。真的是这样吗？

    现在做个实验验证一下，在下文中如果没有特殊的说明，所有的叙述都是以数据表没有建立聚集索引为前提的。

     创建一个表:
      Create Table table1
        (
           col1 char(1)
        )
 
           插入2行数据:
                insertinto table11 values('a')
                insertinto table11 values('b')

            然后使用DBCC PAGE命令察看表的结构（为了方便察看，我只保留了Data和Offset Table部分）

DATA:

 

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x44EFC060

00000000: 10000500 610100fe 08†††††††††††††††††....a....

Slot 1, Offset 0x69, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x44EFC069

00000000: 10000500 620100fe 08†††††††††††††††††....b....

OFFSET TABLE:

Row - Offset

1 (0x1) - 105 (0x69)

0 (0x0) - 96 (0x60) 


                          

             Length 后面为9，也就是说每个数据行占了9个字节，通过offset table部分我们也可以计算出每个数据行占用了9个字节。而不是我们所计算的8个字节。现在察看以下数据行到底存储了什么内容，我们以slot0为例：
   
                        10000500 610100fe 08†††††††††††††††††....a.... 
     
          前两位10表示没有可变长度的字段，后面两位00在sqlserver 2005中没有用到;0500表示固定长度的字段所占的长度为5（实际长度加4）;61转换为十进制数位97，表示小写字母a。后面的0100表示固定长度的字段数量为1;剩下fe就是NULL位图了，翻译成2进制数为1111 1110，这说明该行中没有字段为NULL,这和实际情况也是相符的。那么最后两位08代表什么呢？在公开答案之前再作一个试验


     创建一个表:
      Create Table table1
        (
           col1 char(2)
        )
 
       像表内插入2行数据:
                insertinto table11 values('aa')
                insertinto table11 values('bb')

            然后使用DBCC PAGE命令察看表的结构（为了方便察看，我只保留了Data和Offset Table部分）

DATA:

 

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x4500C060

00000000: 10000600 61610100 fe†††††††††††††††††....aa...

Slot 1, Offset 0x69, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP

Memory Dump @0x4500C069

00000000: 10000600 62620100 fe†††††††††††††††††....bb...

OFFSET TABLE:

Row - Offset

1 (0x1) - 105 (0x69)

0 (0x0) - 96 (0x60) 

                                              

                                 

                  现在看一下char(2)的存储情况，通过Length和offset table 可以看到，数据行的长度仍然为9。分析一下具体的数据行，仍然以slot 0为例：
10000600 61610100 fe†††††††††††††††††....aa...

               前两位10表示没有可变长度的字段，后面两位00在sqlserver 2005中没有用到;0600表示固定长度的字段所占的长度为5（实际长度加4）;61转换为十进制数位97，表示小写字母a。后面的0100表示固定长度的字段数量为1;剩下fe就是NULL位图了，翻译成2进制数为1111 1110，这说明该行中没有字段为NULL,这和实际情况也是相符的。

                  通过上面两个实验可以看出:只有一个字段，并且类型和长度为char(1)的表的数据行所占用的空间和只有一个字段，并且类型和长度为char(2)的表的数据行所占用的空间是相等的。为什么会出现这种情况呢？

                实际上，如果一个数据行的长度没有达到规定的最小长度(9 bytes),SQL SERVER会自动在该行的后面填充一个字节，将长度扩展到9。这就是我们之前看到的那个08出现的原因。所以SQLSERVER数据行最小的长度为9，而不是8。

                您可能要问了：为什么SQL SERVER会有这样的规定呢？

               这个规定是为了更新操作(update)建立的龟腚。我们知道RID用来标识数据页数据行，当数据页中的某一行数据被更新以至于现有的数据页无法再容纳该行时，SQLSERVER会将该行移动到新的数据页中。原有的数据行位置并不被其他的数据行占用，而是替换成一个forwarding pointer。该pointer指向那个被更新的数据行的新的位置。而这个pointer大小为9 byte（header占一byte,RID占8byte,RID的构成：4个字节的pageID,2个字节的fileID,2个字节的slotID）。所以sql server为了保证能够成功地将数据行替换成forwarding pointer，规定每个数据行要最少要占用9个byte。

               您可能又要问了，为什么需要那个鸟forwarding pointer呢，直接移动不就ok了。反正数据页和数据页之间是没有关系的，数据页中的数据行又是无序的。要那个forwarding pointer有啥用？？？

              原因是这样的，当数据页中的某一行数据被更新以至于现有的数据页无法再容纳该行时,SQLSERVER会将该行移动到新的数据页中。之后就出现了两个选择：

           1     更新原有数据页中所有数据行的RID，这可能是个非常昂贵的操作（可能有249个索引）。
           2    不更新数据页中的RID,将那个空缺的位置替换成一个forwarding pointer。在以后的查询时按照这个pointer查找纪录。

       

          您可能还有疑问：如果表建立了聚集索引，每个数据行也要占用9byte吗
           是这样的。虽然聚集索引的更新操作有所不同，但是为了方便b-tree转换成heap，也需要保持9个byte

http://www.cnblogs.com/stswordman/archive/2007/08/22/865425.html