如何减少由于数据类型更改而增加的SQL Server表的大小 [英] How to reduce size of SQL Server table that grew from a datatype change

问题描述

我在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），因此char空间的总和约为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表的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！