在 SQL 2008/12 中压缩 VARCHAR - 看不到结果 [英] Compressing VARCHAR in SQL 2008/12 - not seeing results

问题描述

我一直在尝试在 SQL Server 中进行压缩，但到目前为止我还没有看到我预期的结果.

I have been experimenting with compression in SQL Server but so far I have not seen the results that I expected.

为了测试，我创建了一个带有单个 VARCHAR(8000) 列的新表，并在其中插入了 100k 行.每行包含大约 500 个字的文本，使用 ZIP 压缩可以节省 90% 以上的空间.

To test I have created a new table with single VARCHAR(8000) column and inserted 100k rows into it. Each row contains about 500 words of text, which using ZIP compression sees over a 90% saving in space.

我正在使用命令 EXEC sp_estimate_data_compression_savings 'dbo', 'MyTable', NULL, NULL, 'PAGE' ; 来检查使用 PAGE 压缩可以节省多少空间，但它告诉我根本不会有太多.结果如下:

I am using the command EXEC sp_estimate_data_compression_savings 'dbo', 'MyTable', NULL, NULL, 'PAGE' ; to check how much space would be saved using PAGE compression, but it is telling me that there won't be much at all. The results are as follows:

object_name schema_name index_id    partition_number    size_with_current_compression_setting(KB)   size_with_requested_compression_setting(KB) sample_size_with_current_compression_setting(KB)    sample_size_with_requested_compression_setting(KB)
MyTable      dbo        0         1                       94048                                                  93440                               40064                                              39808

这基本上是没有节省的.我哪里出错了?

Which is basically no saving at all. Where am I going wrong?

ps.我已经对 NVARCHAR(4000) 列进行了相同的实验，并且压缩确实在那里显示了节省，但我相信这是因为压缩强制使用 1 个字符而不是两个数据不需要的字符2 个字符.它实际上并不像 ZIP 那样压缩数据.

ps. I have tried the same experiment with NVARCHAR(4000) column, and compression does show savings there, but I believe this is because the compression forcing use of 1 char instead of two where the data doesn't require 2 chars. It doesn't actually compress the data in a way similar to ZIP would.

推荐答案

如果数据被推到行外(这很可能发生在 VARCHAR(8000) 列上)，那么你不会对其进行任何压缩.仅压缩行内数据:

If the data is pushed off-row (which will likely happen on a VARCHAR(8000) column) then you don't get any compression on it. Only the in-row data is compressed:

由于它们的大小，有时存储大值数据类型与特殊用途页面上的普通行数据分开.数据单独存储的数据无法进行压缩.

Because of their size, large-value data types are sometimes stored separately from the normal row data on special purpose pages. Data compression is not available for the data that is stored separately.

这篇关于在 SQL 2008/12 中压缩 VARCHAR - 看不到结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！