剖析SQLSERVER2005的页面结构--表修改的内部处理篇

-狙击手- 2009-12-02 01:46:44

加精

剖析SQLSERVER2005的页面结构

表修改的内部处理篇

全文阅读：剖析SQLSERVER2005的页面结构--表修改的内部处理篇

本篇介绍一下当我们通过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†††††††††††††††††      ...  

*/

......
......
......
......
......
......

在这儿我们再关心一下SQLSERVER如何知道读取像在第三个例子里增加字符类型列大小时生成新列的情况呢，我们分析行记录时，的确表明增加了一列，那SQLSERVER怎么去区别这个字符列从哪个地址开始读取的呢？在前面我说过SQLSERVER把结构的修改反映在元数据上，那么SQLSERVER自然也就是从元数据上下手正确读取相应的数据。



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



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†††††††††††††        ... 





*/