字段字节数变少,保存页数反而增大?

Question

我有个非常大的数据库,数据量大约10亿行左右,20多G,上千个表.为了减小数据量,各表的列都很简略,有:
dt datetime,b numeric(6,2),c int,d bit
其中dt建了一个不具有唯一性的聚集索引.
从数据类型上看,一行的字节数为 8+5+4+1=18字节.
由于dt列占了最长的字节数,于是对它动起了脑筋,因为时间精度只要到分钟,因此想到可以用smalldatetime来替代datetime,它的字节数只有4.
拿其中一个有八十多万行的表做试验,将dt的数据类型改为 smalldatetime,结果发现数据库反而大了一些,以为看错了,再改了几个表,数据库可用空间越来越小,到后来数据库文件也自动增加了.
想不出原因来.
用dbcc checktable检查,当它为datetime数据类型时,第一个表占2708页,可如果改成smalldatetime的话,却占3020页,多占了近三百页哪,10%多!!!
为了检查是不是由于改动而造成的页面增长,将原数据导入到一个没有建索引的空表,导入时把数据类型改小,导入后页数很小,但重建索引后,页数仍然是3020页,可见该索引对该数据类型就得大些.可是,省出来的4个字节哪去了呢?为什么会增加得那么大呢?
是不是我哪儿没考虑到?
还是因为我许久不来CSDN而受到了惩罚?
索引的填充因子为默认值,即0.

Answer 1

由于dt列占了最长的字节数,于是对它动起了脑筋,因为时间精度只要到分钟,因此想到可以用smalldatetime来替代datetime,它的字节数只有4.
拿其中一个有八十多万行的表做试验,将dt的数据类型改为 smalldatetime,结果发现数据库反而大了一些,以为看错了,再改了几个表,数据库可用空间越来越小,到后来数据库文件也自动增加了.你去看一下小麦的博客,修改一列字符类型的应该是增加了一列,在原来的基础上石头大哥,影子老师的也有

Answer 2

http://blog.csdn.net/happyflystone/archive/2009/12/02/4923803.aspx参考

Answer 3

改变dt为smalldatetime后，记得要rebuild一下clustered index，让空间回收掉.

Answer 4

sorry.
可能要显示更新所有行的dt，然后再rebuild clustered index1).
update tb set dt=CAST(dt as smalldatetime) 2)
rebuild index....

Answer 5

正如7樓老大所說 - -
石頭大哥BOLG裏面說的很清楚了
对字符型的列进行修改时，当字节超原来的设定值时，在页面上体现出来的是增加了一列，当修改成小于当初设定值时，页面没有变化。对于整形变化，虽然我们比int变成tinyint了不管是增加还是历史数据仍然点用了int的字节数空间。
順序副石頭大哥的頁面存儲系列-0-
http://blog.csdn.net/happyflystone/archive/2009/12/09/4969909.aspx
http://blog.csdn.net/happyflystone/archive/2009/11/19/4834762.aspx至於樓主所說單獨新建匯入也會出現更多的頁面，，這個就不知了，，

Answer 6

这个要看你是怎么修改数据表结构的,如果是使用Alter语句,那么会在原来的表中添加一个新列,这个情况下,数据库文件空间反而会违你所愿的增涨;但如果是通过企业管理器界面中的修改功能,那么则是将整个表做复制删除操作,所以这个情况下,数据库文件空间不会增涨

Answer 7

我测了下，应该不需要update了.
drop table tb_1
go
create table TB_1(dt datetime,id int,name varchar(20))
go
insert into TB_1 select GETDATE(),1,'adfasdfadsfa'
go 100000create clustered index ix_0 on tb_1(dt)
go
dbcc ind(master,tb_1,-1)
--531 page
go
drop index tb_1.ix_0
go
alter table tb_1 alter column dt smalldatetime
go
create clustered index ix_0 on tb_1(dt)
go
dbcc ind(master,tb_1,-1)
--488 page
go
update TB_1 set dt=CAST(dt as smalldatetime)
go
dbcc ind(master,tb_1,-1)
--1635 page
go
alter index ix_0 on tb_1 rebuild
go
dbcc ind(master,tb_1,-1)
--488 page

Answer 8

*--压缩数据库的通用存储过程 
  
 压缩日志及数据库文件大小 
 因为要对数据库进行分离处理 
 所以存储过程不能创建在被压缩的数据库中 --邹建 2004.03(引用请保留此信息)--*/ /*--调用示例 
 exec p_compdb  'test ' 
--*/ use master  --注意,此存储过程要建在master数据库中 
go if exists (select * from dbo.sysobjects where id = object_id(N '[dbo].[p_compdb] ') and OBJECTPROPERTY(id, N 'IsProcedure ') = 1) 
drop procedure [dbo].[p_compdb] 
GO create proc p_compdb 
@dbname sysname,   --要压缩的数据库名 
@bkdatabase bit=1,   --因为分离日志的步骤中,可能会损坏数据库,所以你可以选择是否自动数据库 
@bkfname nvarchar(260)= ' ' --备份的文件名,如果不指定,自动备份到默认备份目录,备份文件名为:数据库名+日期时间 
as 
--1.清空日志 
exec( 'DUMP TRANSACTION [ '+@dbname+ '] WITH  NO_LOG ') --2.截断事务日志： 
exec( 'BACKUP LOG [ '+@dbname+ '] WITH NO_LOG ') --3.收缩数据库文件(如果不压缩,数据库的文件不会减小 
exec( 'DBCC SHRINKDATABASE([ '+@dbname+ ']) ') --4.设置自动收缩 
exec( 'EXEC sp_dboption  ' ' '+@dbname+ ' ' ', ' 'autoshrink ' ', ' 'TRUE ' ' ') --后面的步骤有一定危险,你可以可以选择是否应该这些步骤 
--5.分离数据库 
if @bkdatabase=1 
begin 
 if isnull(@bkfname, ' ')= ' '  
  set @bkfname=@dbname+ '_ '+convert(varchar,getdate(),112) 
   +replace(convert(varchar,getdate(),108), ': ', ' ') 
 select 提示信息= '备份数据库到SQL 默认备份目录,备份文件名: '+@bkfname 
 exec( 'backup database [ '+@dbname+ '] to disk= ' ' '+@bkfname+ ' ' ' ') 
end --进行分离处理 
create table #t(fname nvarchar(260),type int) 
exec( 'insert into #t select filename,type=status&0x40 from [ '+@dbname+ ']..sysfiles ') 
exec( 'sp_detach_db  ' ' '+@dbname+ ' ' ' ') --删除日志文件 
declare @fname nvarchar(260),@s varchar(8000) 
declare tb cursor local for select fname from #t where type=64 
open tb  
fetch next from tb into @fname 
while @@fetch_status=0 
begin 
 set @s= 'del " '+rtrim(@fname)+ '" ' 
 exec master..xp_cmdshell @s,no_output 
 fetch next from tb into @fname 
end 
close tb 
deallocate tb --附加数据库 
set @s= ' ' 
declare tb cursor local for select fname from #t where type=0 
open tb  
fetch next from tb into @fname 
while @@fetch_status=0 
begin 
 set @s=@s+ ', ' ' '+rtrim(@fname)+ ' ' ' ' 
 fetch next from tb into @fname 
end 
close tb 
deallocate tb 
exec( 'sp_attach_single_file_db  ' ' '+@dbname+ ' ' ' '+@s) 
go 
------------------------------------------------------------------------------------也可在企业管理里收缩日志： 
--收缩数据库 
dbcc shrinkdatabase( '数据库名 ',0,notruncate) 
DUMP TRANSACTION [库名] WITH NO_LOG   
下例将 UserDB 用户数据库中名为 DataFil1 的文件收缩到 7 MB。 USE UserDB 
GO 
DBCC SHRINKFILE (DataFil1, 7) 
下例将 UserDB 用户数据库中的文件减小，以使 UserDB 中的文件有 10% 的可用空间。 DBCC SHRINKDATABASE (UserDB, 10) 
GO 

Answer 9

各位老大,可能我说得没有够详细,各位说的都不能解决问题.
收缩数据库,重建表,重建索引,这些都做过,都不行.
这么说吧,有一个表A,结构如顶楼:
dt datetime,b numeric(6,2),c int,d bit
有7770111条记录.
另建两个表,dt列不同,复制数据后新建索引,其中检查所占页数,得下列结果:
--创建表
CREATE TABLE [dbo].[TB](
[dt] [datetime] NULL,
[a] [numeric](6, 2) NULL,
[b] [int] NULL,
[c] [bit] NULL
) ON [PRIMARY]
go
CREATE TABLE [dbo].[TC](
[dt] [smalldatetime] NULL,
[a] [numeric](6, 2) NULL,
[b] [int] NULL,
[c] [bit] NULL
) ON [PRIMARY]
go
--插入数据
insert into tb select * from ta
insert into tc select * from ta
go
/*
(770111 行受影响)
(770111 行受影响)
*/
dbcc checktable('tb')
dbcc checktable('tc')
go
/*
TB的 DBCC 结果。
对象 'TB' 的 2576 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
TC的 DBCC 结果。
对象 'TC' 的 2188 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--建索引
CREATE CLUSTERED INDEX IX_TB ON dbo.TB
(
dt
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TC ON dbo.TC
(
dt
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
dbcc checktable('tb')
dbcc checktable('tc')
/*
TB的 DBCC 结果。
对象 'TB' 的 2588 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
TC的 DBCC 结果。
对象 'TC' 的 2886 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

Answer 10

额这很神，，
以下是我的测试结果drop table tb_1
go
create table TB_1(dt datetime,id int,name varchar(20))
go
insert into TB_1 select GETDATE(),1,'adfasdfadsfa'
go 100000
dbcc checktable('tb_1')
--TB_1的 DBCC 结果。
--对象 'TB_1' 的 481 页中有 100000 行。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
CREATE CLUSTERED INDEX ix_0 ON dbo.tb_1
    (
    dt
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
dbcc checktable('tb_1')
--TB_1的 DBCC 结果。
--对象 'TB_1' 的 532 页中有 100000 行。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
drop table tb
create table TB(dt smalldatetime,id int,name varchar(20))
go
insert into TB select GETDATE(),1,'adfasdfadsfa'
go 100000dbcc ind(TestDB,tb,-1)
dbcc checktable('tb')
--TB的 DBCC 结果。
--对象 'TB' 的 428 页中有 100000 行。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
CREATE CLUSTERED INDEX ix ON dbo.tb
(
dt
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
dbcc checktable('tb')
--TB的 DBCC 结果。
--对象 'TB' 的 484 页中有 100000 行。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。不知道你是什么情况导致如此效果
你可以考虑足个删除字段排除额外原因

Answer 11

还有更神奇的呢,下面的测试只用了一个列:
--为了防止在数据类型转变过程中使页数增大,先制作备用数据
create table taa(dt datetime)
create table tab(dt smalldatetime)
insert into taa select dt from ta
insert into tab select dt from ta
dbcc checktable('taa')
dbcc checktable('tab')
go
/*
taa的 DBCC 结果。
对象 'taa' 的 1618 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
tab的 DBCC 结果。
对象 'tab' 的 1239 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--创建表
CREATE TABLE [dbo].[TB](
    [dt] [datetime] NULL,
) ON [PRIMARY]
go
CREATE TABLE [dbo].[TC](
    [dt] [smalldatetime] NULL,
) ON [PRIMARY]
go
--插入数据
insert into tb select * from taa
insert into tc select * from tab
go
/*
(770111 行受影响)
(770111 行受影响)
*/
dbcc checktable('tb')
dbcc checktable('tc')
go
/*
TB的 DBCC 结果。
对象 'TB' 的 1618 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
TC的 DBCC 结果。
对象 'TC' 的 1239 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--建索引
CREATE CLUSTERED INDEX IX_TB ON dbo.TB
    (
    dt
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TC ON dbo.TC
    (
    dt
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
dbcc checktable('tb')
dbcc checktable('tc')
go
/*
TB的 DBCC 结果。
对象 'TB' 的 1634 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
TC的 DBCC 结果。
对象 'TC' 的 1933 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
drop table taa,tab,tb,tc
go

Answer 12

排了序也没有用,上面的数据,在插入时写成
insert into tb select * from taa order by dt
insert into tc select * from tab order by dt
最终页数没变.

Answer 13

找出原因了:对重复数据建聚集索引会占用额外的页.
因为dt列数据有重复,如转变为smalldatetime的话重复的更多,因此所占用的页数也更多.
--创建表
CREATE TABLE [dbo].[TB](
    [dt] [datetime] NULL,
) ON [PRIMARY]
go
CREATE TABLE [dbo].[TC](
    [dt] [smalldatetime] NULL,
) ON [PRIMARY]
go
--插入数据
insert into tb select convert(smalldatetime,dt) from ta order by dt
insert into tc select convert(smalldatetime,dt) from ta order by dt
go
/*
(770111 行受影响)
(770111 行受影响)
*/
dbcc checktable('tb')
dbcc checktable('tc')
go
/*
TB的 DBCC 结果。
对象 'TB' 的 1618 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
TC的 DBCC 结果。
对象 'TC' 的 1239 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--建索引
CREATE CLUSTERED INDEX IX_TB ON dbo.TB
    (
    dt
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX IX_TC ON dbo.TC
    (
    dt
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
dbcc checktable('tb')
dbcc checktable('tc')
go
/*
TB的 DBCC 结果。
对象 'TB' 的 2315 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
TC的 DBCC 结果。
对象 'TC' 的 1933 页中有 770111 行。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
drop table tb,tc
go
先转为相同数据,再插入.则datetime类型的占用页数更多.
谢谢各位!

Answer 14

重复的Clustered Index Key 为额外多出 4个byte 来确定唯一的Key所以转成smalldatetime 後(4byte)，key的长度有可能还是8byte，但不会是原来的12byte(重复的datetime的值)，重建索引后，所占页数会比之前的少，但少多少会依你的smalldatetime的重复数据多少来定.

Answer 15

我是新手。这个也是我想问的问题。楼主能不能把答案放在一楼啊。好多回帖的。
http://www.niupizhijl.com.

Answer 16

dddddddddddddddddddddddddddddddddddddddddddddd

Answer 17

由于dt列占了最长的字节数,于是对它动起了脑筋,因为时间精度只要到分钟,因此想到可以用smalldatetime来替代datetime,它的字节数只有4.
拿其中一个有八十多万行的表做试验,将dt的数据类型改为 smalldatetime,结果发现数据库反而大了一些,以为看错了,再改了几个表,数据库可用空间越来越小,到后来数据库文件也自动增加了.你去看一下小麦的博客,修改一列字符类型的应该是增加了一列,在原来的基础上石头大哥,影子老师的也有 
 

Answer 18

顶，太同意你的这个观点了。 这是将datetime的8个字节改为smalldatetime的4个字节为什么不能节省空间的原因所在。 你将原表的数据拷贝到一个没有任何索引的空表，页面数很少。这个很好理解！直接用alter table命令修改字段的类型时，需要分两种情况：字段增加长度了还是字段缩小长度了。比较关键的是：这个字段以前定义是的变长的还是定长的？如果将一个定长的字段改为变长的，空间反而会增加。 
你的这个问题，我感觉只要在索引页面上。

Answer 19

　我的搜藏：http://sousb.com/books/，这些书多是沉淀下来的极品，涵盖了C/C++，JAVA，Windows程序设计，MFC，数据结构和算法，软件工程，Linux，OpenGL/D3D 这些方向。