name Compressing VARCHAR in SQL 2008/12-not seeing results
sql server compression (4)
Check out the "large value types out of row" option in SQL Server, if it's a VARCHAR(MAX) column. The documentation is worth reading, because setting the option does not immediately convert the data in the table.
sp_tableoption N'MyTable', 'large value types out of row', 'OFF'
I have been experimenting with compression in SQL Server but so far I have not seen the results that I expected.
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.
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. 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.
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.
to show how large a row can be before out of row storage is used:
select OBJECTPROPERTY(Object_id('TableName'), 'TableTextInRowLimit')
to specify in row storage for a table:
sp_tableoption 'TableName', 'text in row', 'ON'
How to keep data in-row in SQL Server
To see if a value is in-row or off-row you can use
A way to force a
VARCHAR(N) column to be in-row (not a
VARCHAR(MAX) is to make it part of the clustered index key. This of course limits the length of the field to the maximum index key size of 900.