剖析SQLSERVER2005的页面结构--表修改的内部处理篇
|
剖析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,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,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,-1)--239 dbcc traceon(3604) dbcc page(testcsdn,1) ? drop table altertc ? /* Slot 0,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,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,col char(100),int_col int) go insert into altertb select REPLICATE('a',5),1 union all select REPLICATE('b',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,237,1) ? drop table altertb /* DATA: Slot 0,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,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,DumpStyle BYTE ? Record Type = PRIMARY_RECORD???????? Record Attributes =? NULL_BITMAP Memory Dump @0x438DC146 ? 00000000:?? 10003801 01000000 61616161 61202020 ?..8.....aaaaa00000010:?? 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. （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |