一个字节引发的存储空间问题

来自：http://blog.youkuaiyun.com/herowang/article/details/4436605

/***************************************************

          作者：herowang(让你望见影子的墙）

    日期：2009.11.4

          注：    转载请保留此信息

    更多内容，请访问我的博客：blog.youkuaiyun.com/herowang

****************************************************/

一个字节引发的存储空间问题

今天在偶然看到了一篇文章，忽然想起疯子的一个帖子中，一直没有想明白的问题，可能和这个有关系，验证了一下，果然如此。

一、起因：

-- 创建2个测试表

CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)

CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)

go

-- 填充数据

declare @i int

set @i = 0

while(@i < 20)

begin

    insert Table_2019(Data) values('')

    insert Table_2020(Data) values('')

    select @i = @i + 1

end

go

查看每个表所占用的空间，结果：

Table_2020 表数据占了 160kb ，Table_2019 表数据占了 80 kb

二、问题

两张表中的字符长度相差为1，（因为数据类型为nchar，所以数据所占用的空间实际相差为2），但是两张表所占用的空间确相差一倍，原因何在？

三、分析

要解释这个问题，首先先要搞清楚sql server中表的存储结构：

那么在表中存储一行数据，除了本身的数据之外，至少还需要1+1+2+2+1=7个字节。

所以对于Table_2019来说，需要7+2019*2=4045个字节的空间，

    对于Table_2020来说，需要7+2020*2=4047个字节的空间。

对于数据库的一页而言（最小的存储单位），一页的空间为8K（8192B）。一个数据页是由3部分组成：页头、数据行和行偏移矩阵，页头保存了页的编号、上一页ID、下一页ID、可以字节数等等关于该页的基本信息。页头的大小是固定的96个字节，所以剩下8192-96=8096个字节用于存储数据行和行偏移矩阵。

行偏移矩阵在页的最后面，而且是倒序排列的，使用2个字节来表示数据行在页面内部的偏移量，有1行数据则行偏移矩阵的大小是2字节，有2行数据则行偏移矩阵的大小是4字节，以此类推。

下面我们再来分析下这两张表：

1、table_2019：存储数据需要的空间为4045B，加上也最后面的行偏移，为4047B，而一个数据库页剩余的空间为8096B，这样的话，一个数据能够存储两行的数据。8096>4047*2=8094

2、table_2020：存储数据需要的空间为4047B，加上也最后面的行偏移，为4049B，而一个数据库页剩余的空间为8096B，这样的话，一个数据只能够存储一行的数据。8096<4049*2=8098。

     这样，我们就会发现，同样的数据，对于table_2020所需要的存储空间是table_2019的二倍。

四、建议

    从上面就可以发现，在对表的设计过程当中，为某一列选择适当的数据类型是非常重要的。尤其是对字符类型，长度的设置一定要合适，尽量让一页能够存储更多行的数据，来减少I/O，提高数据库的效率。