I inhereited a database with tables with many nvarchar columns. The tables were getting fairly large, and I decided to change the datatypes to varchar to cut storage because we do not use international characters. The "data space" on the table (Right-click, then "Properties") has not changed. However, if I copy this table into a new table, the data space used there is about 60% the size of the original table.
我固有一个包含许多nvarchar列的表的数据库。这些表变得相当大，我决定将数据类型更改为varchar以减少存储，因为我们不使用国际字符。表上的“数据空间”（右键单击，然后是“属性”）没有更改。但是，如果我将此表复制到新表中，那么使用的数据空间大约是原始表大小的60％。
I have used 'DBCC CLEANTABLE' in other instances to reclaim space after DROPPING a variable-length column. But this doesn't seem to work when CHANGING variable length data types. How do I go about getting this space back?
在其他情况下，我使用'DBCC CLEANTABLE'来删除可变长度列之后的空间。但是，当改变可变长度数据类型时，这似乎不起作用。我该如何重新获得这个空间？
For those not familiar with DBCC CLEANTABLE, MS has a good article on it with sample code against AdventureWorks.
对于那些不熟悉DBCC CLEANTABLE的人，MS有一篇很好的文章，其中包含针对AdventureWorks的示例代码。
http://msdn.microsoft.com/en-us/library/ms174418(SQL.90).aspx
http://msdn.microsoft.com/en-us/library/ms174418(SQL.90).aspx
2 个解决方案
#1
4
Rebuild the clustered index if the DBCC does not work.
如果DBCC不起作用，请重建聚簇索引。
#2
0
As BOL specifies CLEANTABLE only free's up pages from variable length columns. Your databse is probably fragmented, which occurs normally during database operation. It looks like SQL Server considers a fragmented page as being in use and does not display it as space available (btw sp_spaceused calls this unallocated space)
由于BOL指定CLEANTABLE只有可变长度列的空闲页面。您的数据库可能是碎片，通常在数据库操作期间发生。看起来SQL Server认为碎片页面正在使用中，并且不会将其显示为可用空间（btw sp_spaceused调用此未分配的空间）
You can see how fragmented your tables are with DBCC SHOWCONTIG.
您可以通过DBCC SHOWCONTIG查看表的碎片程度。
By re-building all the indexes in your database, you will get rid of fragmentation, which is pretty much what happens when you copy this data somewhere else.
通过重新构建数据库中的所有索引，您将摆脱碎片，这几乎就是将数据复制到其他位置时所发生的情况。
#1
4
Rebuild the clustered index if the DBCC does not work.
如果DBCC不起作用，请重建聚簇索引。
#2
0
As BOL specifies CLEANTABLE only free's up pages from variable length columns. Your databse is probably fragmented, which occurs normally during database operation. It looks like SQL Server considers a fragmented page as being in use and does not display it as space available (btw sp_spaceused calls this unallocated space)
由于BOL指定CLEANTABLE只有可变长度列的空闲页面。您的数据库可能是碎片，通常在数据库操作期间发生。看起来SQL Server认为碎片页面正在使用中，并且不会将其显示为可用空间（btw sp_spaceused调用此未分配的空间）
You can see how fragmented your tables are with DBCC SHOWCONTIG.
您可以通过DBCC SHOWCONTIG查看表的碎片程度。
By re-building all the indexes in your database, you will get rid of fragmentation, which is pretty much what happens when you copy this data somewhere else.
通过重新构建数据库中的所有索引，您将摆脱碎片，这几乎就是将数据复制到其他位置时所发生的情况。