Alter修改表结构对数据存储的影响
每当我们发现表的结构不正确的话，一般都会使用alter语句对表的结构进行修改，但是alter表结构，会引起一些开销，但这些开销，我们很可能就会忽视。但是这些开销在某些情况下，会给我们的数据库带来很大的影响，例如：对于数据的存储空间，有可能会引起数据库存储空间的急剧膨胀。这个有没有耸人听闻呢？下面就用例子来说明这一点。
基本的思路与要求：
1、 首先清楚数据行在sql 中是如何存储的。可以参见：
http://blog.csdn.net/HEROWANG/archive/2009/11/04/4769430.aspx
2、在验证的过程中会使用到两个命令：
DBCC IND、DBCC PAGE
一、问题：
Use test
go
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1), col char(985))
insert into [tb]
select 'aaaa' union all
select 'bbbb' union all
select 'cccc' union all
select 'dddd' union all
select 'eeee' union all
select 'ffff' union all
select 'gggg' union all
select 'hhhh' union all
select 'iiii' union all
select 'jjjj'
exec sp_spaceused 'tb'
|
name |
rows |
reserved |
data |
index_size |
unused |
|
tb |
10 |
24 KB |
16 KB |
8 KB |
0 KB |
所占用的数据页为2页，16K，按照数据行在页面中的存储方式可以计算出来：
存储一行数据需用的空间：（7+4+985+2）=998B，
1一个数据页可以存储的行数为：8096/998=8.1，所以需要两个页面来进行存储。
下面修改表的结构：
alter table tb
alter column col char(1000)
exec sp_spaceused 'tb'
|
name |
rows |
Reserved |
data |
index_size |
unused |
|
tb |
10 |
32 KB |
24 KB |
8 KB |
0 KB |
按照数据行在页面中的存储方式来计算：
修改后每一行的数据的空间应该为：（7+4+1000+2）=1013B
1一个数据页可以存储的行数为：8096/1013=7.99，所以似乎需要两个页面来进行存储，但是实际上修改后占用了3个页面。那么问题出在什么地方呢？原因在于当我们对表的结构进行修改的时候，对数据的存储产生了很大影响。
二、建立测试环境
Use test
go
if object_id('tb') is not null
drop table tb
go
create table tb(id int identity(1,1), col char(985))
insert into [tb]
select 'aaaa' union all
select 'bbbb' union all
select 'cccc' union all
select 'dddd' union all
select 'eeee' union all
select 'ffff' union all
select 'gggg' union all
select 'hhhh' union all
select 'iiii' union all
select 'jjjj'
三、DBCC IND、DBCC PAGE
1、DBCC IND
DBCC IND
(
['dbname'|dbid], -- 数据库名或ID
tbname, -- 表名
Printopt｜noclustered index_id [, --输出选项
Partition_num] –-指定分区号，主要兼容2000
)
Printopt : --输出选项（常用的）
-- noclustered index_id 所有IAM、数据及指定索引的分页信息
-- -2 所有IAM页面
-- -1 所有的数据、索引、IAM、行溢出及LOB页面
-- 0 行内数据、行内数据的IAM页面
-- 1 聚集索引及所有数据、IAM、LOB页面
例：DBCC IND(test,tb,0)
注：因为输出列数比较多，只截取了一部分图。在这里主要关注最后一列 pagetype
Pagetype为1，说明该页为数据页。
2、DBCC PAGE
DBCC TRACEON(3604)
-- 必须先打开跟踪3604来让DBCC PAGE的结果输出给客户端。
DBCC PAGE(test,1,114,1)
--1为上图的pagefid，114为pagepid，
--1为输出方式，对每记录行分别输出缓冲及页面报头，行偏离表
结果：（只关注一些在这里要用到的数据）
PAGE: (1:114)--查看的是数据页
BUFFER: -- 当前页面调入内存时，要为了便于管理内存中这个页面生成的一种结构
PAGE HEADER:--96个字节页头部结构
DATA: --数据部分
Slot 0, Offset 0x60, Length 996, DumpStyle BYTE
/**************
Offset 0x60：第一行的偏移量，为前面的96个头部结构，0x60=96
Length 996：数据的长度：996=7（存储每行数据需要的空间）+4（第一列的int长度）+985（第二例char的长度）
*************/
00000000: 1000e103 01000000 61616161 20202020
……省略
000003E0: 200200fc
0200：该表有两列
Slot 1, Offset 0x444, Length 996, DumpStyle BYTE
/**************
Offset 0x444：第二行的偏移量，0x444=1092=996（第一行的长度）+96（头部结构）
*************/
…… 省略
OFFSET TABLE:/*每一行的偏移量*/
Row - Offset
7 (0x7) - 7068 (0x1b9c)
6 (0x6) - 6072 (0x17b8)
5 (0x5) - 5076 (0x13d4)
4 (0x4) - 4080 (0xff0)
3 (0x3) - 3084 (0xc0c)
2 (0x2) - 2088 (0x828)
1 (0x1) - 1092 (0x444)
0 (0x0) - 96 (0x60)
四、查看修改表结构后的页面数据
alter table tb
alter column col char(1000)
DBCC IND(test,tb,0)
DBCC TRACEON(3604)
DBCC PAGE(test,1,114,1)
结果：（只关注这里要用到的数据）
DATA:
Slot 0, Offset 0x60, Length 9, DumpStyle BYTE
00000000: 04af0000 00010002 00 –第一行的数据
Slot 1, Offset 0x69, Length 1996, DumpStyle BYTE
00000000: 1000c907 02000000 62626262 20202020 †........bbbb
……
000003E0: 20626262 62202020 20202020 20202020 † bbbb
……
000007C0: 20202020 20202020 200300f8
观察这里的数据，我们会发现这么两个问题：
1、第一行数据slot 0 去哪儿了？怎么只剩下9个字节？
2、第二行数据slot 1，长度 length 为什么是1996，而不是上面计算的1013呢？
仔细看下第二行的数据，就会发现bbbb出现了两次，所以1996是在原来的基础上，再加了1000，即当我们修改表的时候，并不是修改列的空间，而是在每一行后面增加新的一列。所以1996=7+4+985+1000。而倒数第三个字节也说明了这一点：0300 说明该表现在有三列，而不是原来的两列。
第一个问题暂时保留，接着往下看，就会发现答案
DBCC PAGE(test,1,175,1)（第二个数据页）
结果：
DATA:
Slot 0, Offset 0x1014, Length 1996, DumpStyle BYTE
1000c907 09000000 69696969 20202020 †........iiii
……
20202020 20202020 200300f8
Slot 1, Offset 0x17e0, Length 1996, DumpStyle BYTE
1000c907 0a000000 6a6a6a6a 20202020 †........jjjj
……
20202020 20202020 200300f8
Slot 2, Offset 0x60, Length 2010, DumpStyle BYTE
3200c907 01000000 61616161 20202020 †2.......aaaa
……
20202020 20202020 200300f8 0100da87 † .......
00047200 00000100 0000
Slot 3, Offset 0x83a, Length 2010, DumpStyle BYTE
3200c907 03000000 63636363 20202020 †2.......cccc
20202020 20202020 200300f8 0100da87
00047200 00000100 0200
OFFSET TABLE:（第二页的行偏移）
Row - Offset
3 (0x3) - 2106 (0x83a)
2 (0x2) - 96 (0x60)
1 (0x1) - 6112 (0x17e0)
0 (0x0) - 4116 (0x1014)
观察这里的数据：上面的第一个问题视乎找到了答案。
1、 因为第一页的数据需要分页，所以就把第一行的数据和第三行数据，放到了第二个页。
而第一页的其他两行数据因为第二页放不下，所以放在第三页中。
2、 第三行也就是slot2，存储的恰好就是从第一页分出来的aaaa这一行的数据，但是为什么它的偏移是96，也就是说在第二数据页的第一行存储的是aaaa，而不是原来的iiii。
原因：当我们修改表的结构，会把原来的数据向后移动，这样原来的空间就空出来。这个时候，把从第一页分离出来的数据，就写到第二页里面，这样，aaaa恰好写在第一页，所以他的偏移为96，而原来的iiii和jjjj反而不在原来的地方。
如下图所示：
接着往下看：
DBCC PAGE(test,1,45,1)
DATA:
Slot 0, Offset 0x452, Length 2010, DumpStyle BYTE
00000000: 3200c907 05000000 65656565 20202020 .......eeee
……
000007C0: 20202020 20202020 200300f8 0100da87
000007D0: 00047200 00000100 0400
Slot 1, Offset 0x101e, Length 2010, DumpStyle BYTE
00000000: 3200c907 07000000 67676767 20202020.......gggg
……
000007C0: 20202020 20202020 200300f8 0100da87
000007D0: 00047200 00000100 0600
……
OFFSET TABLE:
Row - Offset （第二页的行偏移）
1 (0x1) - 4126 (0x101e)
0 (0x0) - 1106 (0x452)
最后的几个问题：
1、综合观察上面的数据，有些行的长度为1996，而有些行的长度为2010。而长度为2010的行，恰好都是从第一页分出来的数据。然后结合上面的二进制数据，似乎可以得到这个结果：
1）、没有分离的数据，那么在后面加上一个新列，长度为修改后的列的长度
1996=7+4+985+1000;
2）、而被分离的数据，除了加上新列，还有在后面加上一个长度为14字节的数据，至于这14个字节做什么，还尚不清楚。但是其中最后字节数据很有趣：
0000 aaaa
0200 cccc
0400 eeee
0600 ffff
恰好是该数据在原来表中行数，巧合还是有一定意义？
五、总结：
1、修改表的时候，并不是扩充原来列的存储空间，而是在表的后面增加一个新的列
2、增加了新列后，可能会引起数据的分页。如果原来的数据不分页的话，那么数据就整体向后移动。如果要分页的话，那么最后一页的数据，向后移动后，前面就留有空间，先向最后一页的空闲空间写数据，如果写不下，则分配新的数据页，来存储数据。
3、如果数据进行分页后，对于没有分离的数据，那么在后面增加一个新列；对于分离的数据，除了增加一个新列外，还额外增加14字节的数据
这样，我们基本上就能估算出，修改表，需要的存储空间上的开销了（只能是大概的，因为对于分离的数据，在原来的数据页上还会保留9个字节空间，对于分页后的数据后面还有14个字节的空间），修改表结构后，存储每行数据需要的空间为：7+4+1985+2=1998B
这样每页最多只能存储4行数据。
如果该表的数据量很大的话，这个开销就会很大，我们是不能忽视这个开销的。
六、解决方法：
上有政策，下有对策。本方法实际的实际可行性，没有验证。（没有生产环境，不好测试）。
1、 新建一张表，当然是修改结构后的表
2、 Insert into tb2 Select * from tb1
3、 重命名tb2为tb1
七、尚存的问题：
1、分页的行，在原来数据页中保留的九个字节的作用？
2、分页的数据，最后加的14个字节的作用
3、最后一页的行偏移是怎么偏移的。
4、在前面，我们已经看到，修改表的结构其实是在表的后面增加一个新的列，那么sql server如何知道从哪块开始读取该列的数据呢？
要来解答这三个问题，需要从上面的DBCC中取出如下的数据，然后进行分析，就可以知道第一个和第二个问题的答案了。
|
原来数据的九个字节 |
分页后数据的14个字节 |
|
04af0000 00010002 00 |
0100da87 00047200 00000100 0000 |
|
04af0000 00010003 00 |
0100da87 00047200 00000100 0200 |
|
042d0000 00010000 00 |
0100da87 00047200 00000100 0400 |
|
042d0000 00010001 00 |
0100da87 00047200 00000100 0600 |
1、分页的行，在原来数据页中保留的九个字节的作用？
数据分页以后，在原来的位置会有一个长度为9个字节的数据，这个数据的作用是：给出了数据转移到什么位置。第一个字节 04为状态位；下面四个字节af0000 00为页面id，转换为十进制为175，恰好就是aaaa数据转移后所在的页面号；下面的两个字节0100为文件号；最后的两个字节为数据转移后的所在数据页面的slot号。
2、分页的数据，最后加的14个字节的作用
分页后数据的14个字节：前面六个字节0100da870004 ，暂时只发现只要转移过，都是这个值，具体含义尚不清楚。后面的8个字节的左右是：给出了这个数据原来的位置。
第七个到第十个字节7200 0000（转换为十进制为144）为数据原来的数据页。下面的两个字节0100为文件号；最后的两个字节为数据转移前的所在数据页面的slot号。
3、最后一页的行偏移是怎么偏移的。
1）、当数据少于8个页面时使用混合类型的区
2）、SQLSERVER对页面原使用原则在磁盘空间不足时才考虑回收 ，当一个历史页面被使用后删除数据时是不会触动数据的，所以混合区给调用后直接根据页面上的freeData跳至指定区写入数据
3）、因为我们都是在堆表上进行的
