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

剖析SQLSERVER2005的页面结构

表修改的内部处理篇

                                happyflystone

                                            Blog: http://blog.csdn.net/happyflystone

 

       本篇介绍一下当我们通过alter table来修改数据表的列类型时因SQLSERVER只检查不修改数据而造成让大家迷惑的假象,或说是让人费解的地方。在这里我们只介绍三种情况:一增加列(有无缺省);二,删除列;三修改列;在大多数的情况下,SQLSERVER只要修改元数据而可能不会去触碰页面上的物理数据,所以如果我们通过dbcc page来查看页面的内容时会有点让人晕头转向,下面们先简单的说三种情况,最后做一些的解释。

 

一:增加列(有无缺省)

---------------------------------------------------------------------

-- Author : HappyFlyStone

-- Date   : 2009-12-01 220000

-- 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 220000

-- 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 220000

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

 

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

 

 

*/

大家注意到没有,我在结果显示里的红色加下划线的部分,SQLSERVER通过元数据可以很方清楚的知道altertb的列col从行记录的0x0070位置开始长度为200.

 

    Over!请大家关注我的blog.

   

展开阅读全文
©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值