文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >Server 2005表太大

问Server 2005表太大
EN

Stack Overflow用户
提问于 2013-12-12 16:54:57
回答 1查看 108关注 0票数 0

我有这张桌子。

我不知道为什么这张桌子太大了。

这是桌子的结构。

代码语言：javascript
复制
    CREATE TABLE [dbo].[ACI_HISCLI](
    [TER_CODI] [varchar](13) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_NUME] [int] NOT NULL,
    [HIS_FECH] [datetime] NOT NULL,
    [HIS_ESTA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_HORA] [datetime] NOT NULL,
    [MED_CODI] [decimal](13, 0) NOT NULL,
    [HIS_HOEN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_HODC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_MOTI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AODS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AODC] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AOSS] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_AOSC] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ODQ1] [decimal](4, 2) NULL,
    [HIS_ODQ2] [int] NULL,
    [HIS_ODQ3] [decimal](4, 2) NULL,
    [HIS_ODQ4] [int] NULL,
    [HIS_OSQ1] [decimal](4, 2) NULL,
    [HIS_OSQ2] [int] NULL,
    [HIS_OSQ3] [decimal](4, 2) NULL,
    [HIS_OSQ4] [int] NULL,
    [HIS_ODR1] [decimal](4, 2) NULL,
    [HIS_ODR2] [decimal](4, 2) NULL,
    [HIS_ODR3] [int] NULL,
    [HIS_OSR1] [decimal](4, 2) NULL,
    [HIS_OSR2] [decimal](4, 2) NULL,
    [HIS_OSR3] [int] NULL,
    [HIS_ODS1] [decimal](4, 2) NULL,
    [HIS_ODS2] [decimal](4, 2) NULL,
    [HIS_ODS3] [int] NULL,
    [HIS_ODS4] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OSS1] [decimal](4, 2) NULL,
    [HIS_OSS2] [decimal](4, 2) NULL,
    [HIS_OSS3] [int] NULL,
    [HIS_OSS4] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ADI1] [decimal](3, 2) NULL,
    [HIS_ADI2] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ADI3] [int] NULL,
    [HIS_TON1] [datetime] NULL,
    [HIS_TOOD] [int] NULL,
    [HIS_TOOS] [int] NULL,
    [HIS_MOVI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIOM] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_FOJO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OTRO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DIA_COD1] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DIA_COD2] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [DIA_COD3] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_COND] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBSE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_IMA1] [image] NULL,
    [HIS_IMN1] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_IMA2] [image] NULL,
    [HIS_IMN2] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_CONS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ORIL] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ORIT] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ORIP] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGS] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_INGL] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_TAIN] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_PUIN] [int] NULL,
    [HIS_TEIN] [decimal](6, 2) NULL,
    [HIS_TAEG] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_PUEG] [int] NULL,
    [HIS_TEEG] [decimal](6, 2) NULL,
    [HIS_OBHE] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [USU_CREA] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HIS_ASPR] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PRO_CONS] [int] NULL,
    [MED_CODC] [decimal](13, 0) NULL,
    [ANE_CODI] [decimal](13, 0) NULL,
    [AYU_CODI] [decimal](13, 0) NULL,
    [INS_CODI] [decimal](13, 0) NULL,
    [HIS_DIPR] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIPO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DECI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_GODV] [int] NULL,
    [HIS_GODT] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_GOIV] [int] NULL,
    [HIS_GOIT] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIFO] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIOT] [varchar](51) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBSD] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBSS] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ODLA] [image] NULL,
    [HIS_ODFR] [image] NULL,
    [HIS_OILA] [image] NULL,
    [HIS_OIFR] [image] NULL,
    [HIS_ODSE] [image] NULL,
    [HIS_OISE] [image] NULL,
    [HIS_GOD1] [decimal](3, 2) NULL,
    [HIS_GOD2] [decimal](3, 2) NULL,
    [HIS_GOD3] [decimal](3, 2) NULL,
    [HIS_GOD4] [decimal](3, 2) NULL,
    [HIS_GOD5] [decimal](3, 2) NULL,
    [HIS_GOD6] [decimal](3, 2) NULL,
    [HIS_GOD7] [decimal](3, 2) NULL,
    [HIS_GOD8] [decimal](3, 2) NULL,
    [HIS_GOI1] [decimal](3, 2) NULL,
    [HIS_GOI2] [decimal](3, 2) NULL,
    [HIS_GOI3] [decimal](3, 2) NULL,
    [HIS_GOI4] [decimal](3, 2) NULL,
    [HIS_GOI5] [decimal](3, 2) NULL,
    [HIS_GOI6] [decimal](3, 2) NULL,
    [HIS_GOI7] [decimal](3, 2) NULL,
    [HIS_GOI8] [decimal](3, 2) NULL,
    [HIS_CONT] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_RESU] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_GLIC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_HEMA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_PAQU] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_CREA] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_ELEC] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO1] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO2] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO4] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_BIO5] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBEX] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_OBST] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIT1] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIT2] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIT3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_TANE] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ENT_CODI] [numeric](13, 0) NULL,
    [HIS_DIDR] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIDO] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIPI] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DISA] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIO1] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIO2] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_DIO3] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HIS_HOAN] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_NOAC] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_TACO] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_PACO] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_DACO] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_IACO] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_NORE] [varchar](120) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_TRES] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_PRES] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_DRES] [varchar](70) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [TER_IRES] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [USU_AUDI] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [FEC_AUDI] [datetime] NOT NULL,
    [TIP_AUDI] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [MOD_ORIG] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

使用命令sp_spaceued ACI_HISCLI的

其结果是：

代码语言：javascript
复制
name: ACI_HISCLI
rows: 270719
reserved:   64521288 KB
data:       64377992 KB
index_size:      152 Kb
unused:       143144 KB

类型图像的字段，几乎为空。这个表为用户有如此多的事务，但只是文本和大小是60 GB !！我不知道为什么这张桌子这么大！如何优化此表或缩小此表。我试着：

代码语言：javascript
复制
DBCC CLEANTABLE

重建索引谢谢。

bigdata
sql
sql-server
EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2013-12-12 16:58:56

所有这些文本和图像字段各占16个字节，因为它们都是指向数据实际所在位置的指针(当有数据时)。但我几乎可以肯定，就像固定宽度的数据类型(INT、BIGINT、DATETIME等)。即使是空的时候，它们也占据了这个空间。

而且，表中的字段太多，难以管理。您应该考虑将其分解为两个(或多个)表，这些表具有相同的PK，是FKed，并且具有1到1的关系。不经常使用的字段应移到辅助表中。

编辑：

使用的数据空间也受聚集索引上的FILLFACTOR设置的影响。将其设置得太低，特别是对于这么大的行大小，将导致很少的行适合8k数据。

此外，有时删除行时不释放空间。在聚集索引上进行索引重建会释放出该空间(如果大小下降了很多，那么在进行重建之后，您将知道是否有未释放的空间)。

EDIT2：

任何使用Server 2005和更新版本的用户都应该将使用旧blob类型(现在已不再推荐)的字段转换为较新的对应项：

  • 文本-> VARCHAR(最大)
  • NTEXT -> NVARCHAR(最大)
  • 图像->变元(最大)
票数 3
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/20549443

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档