如何减少因数据类型更改而增长的 SQL Server 表的大小

How to reduce size of SQL Server table that grew from a datatype change(如何减少因数据类型更改而增长的 SQL Server 表的大小)

问题描述

我在 SQL Server 2005 上有一个大约 4GB 的表.

I have a table on SQL Server 2005 that was about 4gb in size.

(约 1700 万条记录)

(about 17 million records)

我将其中一个字段从数据类型 char(30) 更改为 char(60)(总共有 25 个字段，其中大部分是 char(10) 所以字符空间加起来大约是 300)

I changed one of the fields from datatype char(30) to char(60) (there are in total 25 fields most of which are char(10) so the amount of char space adds up to about 300)

这导致表格大小翻倍(超过 9GB)

This caused the table to double in size (over 9gb)

然后我将 char(60) 更改为 varchar(60) 然后运行一个函数从数据中删除额外的空格(以减少平均字段中数据的长度大约为 15)

I then changed the char(60) to varchar(60) and then ran a function to cut extra whitespace out of the data (so as to reduce the average length of the data in the field to about 15)

这并没有减少表的大小.缩小数据库也无济于事.

This did not reduce the table size. Shrinking the database did not help either.

除了实际重新创建表结构并复制数据(那是 1700 万条记录！)之外，是否有一种不那么激烈的方法可以再次减小大小?

Short of actually recreating the table structure and copying the data over (that's 17 million records!) is there a less drastic way of getting the size back down again?

推荐答案

很明显，您没有获得任何空间！:-)

Well it's clear you're not getting any space back ! :-)

当您将文本字段更改为 CHAR(60) 时，它们都会被空格填满.所以你所有的字段现在真的有 60 个字符长.

When you changed your text fields to CHAR(60), they are all filled up to capacity with spaces. So ALL your fields are now really 60 characters long.

将其改回 VARCHAR(60) 无济于事 - 字段仍然都是 60 个字符长....

Changing that back to VARCHAR(60) won't help - the fields are still all 60 chars long....

您真正需要做的是对所有字段运行 TRIM 函数，将它们减少回修剪后的长度，然后进行数据库收缩.

What you really need to do is run a TRIM function over all your fields to reduce them back to their trimmed length, and then do a database shrinking.

完成此操作后，您需要重建聚集索引以回收一些浪费的空间.聚集索引实际上是您的数据所在的位置 - 您可以像这样重建它:

After you've done that, you need to REBUILD your clustered index in order to reclaim some of that wasted space. The clustered index is really where your data lives - you can rebuild it like this:

ALTER INDEX IndexName ON YourTable REBUILD 

默认情况下，您的主键是您的聚集索引(除非您另外指定).

By default, your primary key is your clustered index (unless you've specified otherwise).

马克

这篇关于如何减少因数据类型更改而增长的 SQL Server 表的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！