Alter修改表结构对数据存储的影响（一）

 

/***************************************************

          作者：herowang(让你望见影子的墙）

    日期：2009.11.27

          注：    转载请保留此信息

    更多内容，请访问我的博客：blog.csdn.net/herowang

****************************************************/

Alter修改表结构对数据存储的影响（一）

每当我们发现表的结构不正确的话，一般都会使用alter语句对表的结构进行修改，但是alter表结构，会引起一些开销，但这些开销，我们很可能就会忽视。但是这些开销在某些情况下，会给我们的数据库带来很大的影响，例如：对于数据的存储空间，有可能会引起数据库存储空间的急剧膨胀。这个有没有耸人听闻呢？下面就用例子来说明这一点。

基本的思路与要求：

1、  首先清楚数据行在sql 中是如何存储的。可以参见：

http://blog.csdn.net/HEROWANG/archive/2009/11/04/4769430.aspx
2、在验证的过程中会使用到两个命令：

DBCC IND、DBCC PAGE

一、问题：

Use test

go

if object_id('tb') is not null

  drop table tb

go

create table tb(id int identity(1,1), col char(985))

insert into [tb]

select 'aaaa' union all

select 'bbbb' union all

select 'cccc' union all

select 'dddd' union all

select 'eeee' union all

select 'ffff' union all

select 'gggg' union all

select 'hhhh' union all

select 'iiii' union all

select 'jjjj'

  exec sp_spaceused 'tb'

   name

rows

reserved

data

index_size

unused

tb

10        

24 KB

16 KB

8 KB

0 KB

    所占用的数据页为2页，16K，按照数据行在页面中的存储方式可以计算出来：

存储一行数据需用的空间：（7+4+985+2）=998B，

1一个数据页可以存储的行数为：8096/998=8.1，所以需要两个页面来进行存储。

 下面修改表的结构：

alter table tb

alter column col char(1000)

exec sp_spaceused 'tb'

name

rows

Reserved

data

index_size

unused

tb

10        

32 KB

24 KB

8 KB

0 KB

按照数据行在页面中的存储方式来计算：

修改后每一行的数据的空间应该为：（7+4+1000+2）=1013B

1一个数据页可以存储的行数为：8096/1013=7.99，所以似乎需要两个页面来进行存储，但是实际上修改后占用了3个页面。那么问题出在什么地方呢？原因在于当我们对表的结构进行修改的时候，对数据的存储产生了很大影响。

二、建立测试环境

Use test

go

if object_id('tb') is not null

  drop table tb

go

create table tb(id int identity(1,1), col char(985))

insert into [tb]

select 'aaaa' union all

select 'bbbb' union all

select 'cccc' union all

select 'dddd' union all

select 'eeee' union all

select 'ffff' union all

select 'gggg' union all

select 'hhhh' union all

select 'iiii' union all

select 'jjjj'

三、DBCC IND、DBCC PAGE

1、DBCC IND

DBCC IND
(　

['dbname'|dbid], -- 数据库名或ID
　　tbname, -- 表名

Printopt｜noclustered index_id [, --输出选项

Partition_num] –-指定分区号，主要兼容2000

)

Printopt : --输出选项（常用的）

-- noclustered index_id  　所有IAM、数据及指定索引的分页信息  

-- -2   所有IAM页面

-- -1   所有的数据、索引、IAM、行溢出及LOB页面

--  0   行内数据、行内数据的IAM页面

--  1   聚集索引及所有数据、IAM、LOB页面

例：DBCC IND(test,tb,0)

 

 

注：因为输出列数比较多，只截取了一部分图。在这里主要关注最后一列 pagetype

Pagetype为1，说明该页为数据页。

2、DBCC PAGE

DBCC TRACEON(3604)

-- 必须先打开跟踪3604来让DBCC PAGE的结果输出给客户端。

DBCC PAGE(test,1,114,1)

--1为上图的pagefid，114为pagepid，

--1为输出方式，对每记录行分别输出缓冲及页面报头，行偏离表

结果：（只关注一些在这里要用到的数据）

PAGE: (1:114)--查看的是数据页

BUFFER:      -- 当前页面调入内存时，要为了便于管理内存中这个页面生成的一种结构

PAGE HEADER:--96个字节页头部结构

DATA:        --数据部分

 

Slot 0, Offset 0x60, Length 996, DumpStyle BYTE

/**************

Offset 0x60：第一行的偏移量，为前面的96个头部结构，0x60=96

Length 996：数据的长度：996=7（存储每行数据需要的空间）+4（第一列的int长度）+985（第二例char的长度）       

*************/      

00000000:   1000e103 01000000 61616161 20202020

……省略          

000003E0:   200200fc                    

0200：该表有两列

Slot 1, Offset 0x444, Length 996, DumpStyle BYTE

/**************

Offset 0x444：第二行的偏移量，0x444=1092=996（第一行的长度）+96（头部结构）

*************/ 

…… 省略

OFFSET TABLE:/*每一行的偏移量*/

Row - Offset                        

7 (0x7) - 7068 (0x1b9c)             

6 (0x6) - 6072 (0x17b8)             

5 (0x5) - 5076 (0x13d4)             

4 (0x4) - 4080 (0xff0)              

3 (0x3) - 3084 (0xc0c)              

2 (0x2) - 2088 (0x828)              

1 (0x1) - 1092 (0x444)              

0 (0x0) - 96 (0x60)