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

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

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

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

*/