剖析SQLSERVER2005的页面结构
表修改的内部处理篇
happyflystone
Blog: http://blog.csdn.net/happyflystone
本篇介绍一下当我们通过alter table来修改数据表的列类型时因SQLSERVER只检查不修改数据而造成让大家迷惑的假象，或说是让人费解的地方。在这里我们只介绍三种情况：一增加列(有无缺省)；二，删除列；三修改列；在大多数的情况下，SQLSERVER只要修改元数据而可能不会去触碰页面上的物理数据，所以如果我们通过dbcc page来查看页面的内容时会有点让人晕头转向，下面们先简单的说三种情况，最后做一些的解释。
一：增加列(有无缺省)
---------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-12-01 22：00：00
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: SP2)
-- 转载注明出处及相关信息
---------------------------------------------------------------------
create table altertc(id int identity(1,1),col char(10))
go
insert into altertc
select REPLICATE('a',5) union all
select REPLICATE('b',5)
go
-- 下面的三次执行分别通过dbcc page看页面的变化
alter table altertc
add col2 char(20) –-第一次
go
alter table altertc
add col2 char(20) default 'tt' –-第二次
go
insert into altertc select 'cccc','' –-第三次增加记录
go
dbcc ind(testcsdn,altertc,-1)--239
dbcc traceon(3604)
dbcc page(testcsdn,1,239,1)
drop table altertc
/*
Slot 0, Offset 0x60, Length 21, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x444CC060
00000000: 10001200 01000000 61616161 61202020 †........aaaaa
00000010: 20200200 fc†††††††††††††††††††††††††† ...
-- alter table altertc add col1 char(20)
-- alter table altertc add col2 char(20) default ''
Slot 0, Offset 0x60, Length 21, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x4444C060
00000000: 10001200 01000000 61616161 61202020 †........aaaaa
00000010: 20200200 fc†††††††††††††††††††††††††† ...
--insert into altertc select 'cccc',''
Slot 2, Offset 0x8a, Length 41, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x443CC08A
00000000: 10002600 03000000 63636363 20202020 †..&.....cccc
00000010: 20202020 20202020 20202020 20202020 †
00000020: 20202020 20200300 f8††††††††††††††††† ...
*/
小结：当我们增加列时页面的历史物理存储是不会变化，只是增加的数据才会在页面上体现结构的变化。
二、删除列
create table altertc(id int identity(1,1),col int)
go
insert into altertc
select 1000 union all
select 2000
go
alter table altertc
drop column col –比较删除列前后dbcc page的结果
go
dbcc ind(testcsdn,altertc,-1)--239
dbcc traceon(3604)
dbcc page(testcsdn,1,239,1)
drop table altertc
/*
Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x4472C060
00000000: 10000c00 01000000 e8030000 0200fc††††...............
--alter table altertc drop column col --
Slot 0, Offset 0x60, Length 15, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x434EC060
00000000: 10000c00 01000000 e8030000 0200fc††††...............
*/
小结：当我们删除一个列时，在页面并没有消除所删除列的数据。
三、修改列
------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-12-01 22：00：00
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: SP2)
-- 转载注明出处及相关信息
-------------------------------------------------------------------
create table altertb(id int identity(1,1),col char(100),int_col int)
go
insert into altertb
select REPLICATE('a',5),1 union all
select REPLICATE('b',5),2
go
alter table altertb
alter column col char(200) --
go
alter table altertb
alter column int_col tinyint –- 两个alter column 可同时执行
go
insert into altertb select 'cccc',3 –-观察新增加记录页面情况
go
dbcc ind(testcsdn,altertb,-1)--237
dbcc traceon(3604)
dbcc page(testcsdn,1,237,1)
drop table altertb
/*
DATA:
Slot 0, Offset 0x60, Length 115, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x4343C060
00000000: 10007000 01000000 61616161 61202020 †..p.....aaaaa
00000010: 20202020 20202020 20202020 20202020 †
00000020: 20202020 20202020 20202020 20202020 †
00000030: 20202020 20202020 20202020 20202020 †
00000040: 20202020 20202020 20202020 20202020 †
00000050: 20202020 20202020 20202020 20202020 †
00000060: 20202020 20202020 20202020 01000000 † ....
00000070: 0300f8†††††††††††††††††††††††††††††††...
alter table altertb alter column col char(200) --
go
alter table altertb alter column int_col tinyint --
go
Slot 0, Offset 0x146, Length 315, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x438DC146
00000000: 10003801 01000000 61616161 61202020 †..8.....aaaaa
00000010: 20202020 20202020 20202020 20202020 †
00000020: 20202020 20202020 20202020 20202020 †
00000030: 20202020 20202020 20202020 20202020 †
00000040: 20202020 20202020 20202020 20202020 †
00000050: 20202020 20202020 20202020 20202020 †
00000060: 20202020 20202020 20202020 01000000 † ....
00000070: 61616161 61202020 20202020 20202020 †aaaaa
00000080: 20202020 20202020 20202020 20202020 †
00000090: 20202020 20202020 20202020 20202020 †
......
00000120: 20202020 20202020 20202020 20202020 †
00000130: 20202020 20202020 0400f0††††††††††††† ...
insert into altertb select 'cccc',3
go
Slot 2, Offset 0x3bc, Length 315, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x4343C3BC
00000000: 10003801 03000000 00000000 00ac0100 †..8.............
00000010: 0001001a 000300f8 16171900 0001e08d †................
00000020: 05000000 0000ac01 00000100 1b000300 †................
00000030: f8161819 00000104 43040000 000000ac †........C.......
00000040: 01000001 001c0003 00f81619 19000001 †................
00000050: 20300500 00000000 ac010000 01001d00 † 0..............
00000060: 0300f816 1a190000 0144a705 03000000 †.........D......
00000070: 63636363 20202020 20202020 20202020 †cccc
00000080: 20202020 20202020 20202020 20202020 †
......
00000120: 20202020 20202020 20202020 20202020 †
00000130: 20202020 20202020 0400f2††††††††††††† ...
*/
小结：对字符型的列进行修改时，当字节超原来的设定值时，在页面上体现出来的是增加了一列，当修改成小于当初设定值时，页面没有变化。对于整形变化，虽然我们比int变成tinyint了不管是增加还是历史数据仍然点用了int的字节数空间。
显然，对于小数据量的表来说，SQLSERVER的这种处理方式给我们感觉有点不人性化，但是对于大数据量的来说，对表的修改都去触碰页面数据那极可能将是一个极为耗时的过程，不过现在的硬件资源对小表来说这似乎又不是那么的突出了。
在这儿我们再关心一下SQLSERVER如何知道读取像在第三个例子里增加字符类型列大小时生成新列的情况呢，我们分析行记录时，的确表明增加了一列，那SQLSERVER怎么去区别这个字符列从哪个地址开始读取的呢？在前面我说过SQLSERVER把结构的修改反映在元数据上，那么SQLSERVER自然也就是从元数据上下手正确读取相应的数据。
-------------------------------------------------------------------
-- Author : HappyFlyStone
-- Date : 2009-12-01 22：00：00
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: SP2)
-- 转载注明出处及相关信息
-------------------------------------------------------------------
SELECT
cast(object_name(P.OBJECT_ID) as varchar(10)) as obj_name,
cast(c.name as varchar(10)) as name ,
max_inrow_length,
IPC.system_type_id,IPC.max_length,
CAST(leaf_offset AS BINARY(2)) AS leaf_offset
FROM SYS.SYSTEM_INTERNALS_PARTITION_COLUMNS IPC
JOIN SYS.PARTITIONS P
ON IPC.PARTITION_ID = P.PARTITION_ID
JOIN SYS.COLUMNS C
ON C.COLUMN_ID = PARTITION_COLUMN_ID
AND C.OBJECT_ID = P.OBJECT_ID
WHERE P.OBJECT_ID = OBJECT_ID('altertb')
/*
obj_name name max_inrow_length system_type_id max_length leaf_offset
---------- ------ --------- -------------- ---------- -----------
altertb id 4 56 4 0x0004
altertb col 200 175 200 0x0070
altertb int_col 4 56 4 0x006C
(3 行受影响)
*/
alter table altertb alter column col char(200) --
go
Slot 0, Offset 0x146, Length 315, DumpStyle BYTE
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP
Memory Dump @0x438DC146
00000010: 20202020 20202020 20202020 20202020 †
00000020: 20202020 20202020 20202020 20202020 †
00000030: 20202020 20202020 20202020 20202020 †
00000040: 20202020 20202020 20202020 20202020 †
00000050: 20202020 20202020 20202020 20202020 †
00000060: 20202020 20202020 20202020 01000000 † ....
00000070: 61616161 61202020 20202020 20202020 †aaaaa
00000080: 20202020 20202020 20202020 20202020 †
00000090: 20202020 20202020 20202020 20202020 †
......
00000120: 20202020 20202020 20202020 20202020 †
00000130: 20202020 20202020 0400f0††††††††††††† ...
*/
大家注意到没有，我在结果显示里的红色加下划线的部分，SQLSERVER通过元数据可以很方清楚的知道altertb的列col从行记录的0x0070位置开始长度为200.
Over!请大家关注我的blog.
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
