Contents
When to use compression and decompression in SQL Server?
You can benefit from the COMPRESS and DECOMPRESS functions in compressing big text columns, such as VARCHAR (MAX) or NVARCHAR (MAX) in addition to the large binary data, since the old compression technology at the page and row levels don’t work well with such data types.
What is the syntax for the Compress function?
The basic syntax for the COMPRESS built-in function is: COMPRESS (Expression) Where the Expressions can be any value with nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) data types.
How does the compress and decompression function work?
The Compress and Decompress functions use the Standard GZIP compression algorithm to compress the data itself.
Can a string be passed to the Compress function?
The string to be compressed can be also passed to the COMPRESS function as a parameter, and the result of the COMPRESS function can be passed too to the DECOMPRESS function as below:
What happens when you compress a table in SQL?
If your tables have seen lots of inserts in the middle, updates, or deletes, then maybe not. When you compress a number of tables in a partition, SQL will not defragment the partition. That may mean that the partitions have no space to give up.
When to use columnstore archival compression in SQL?
Use columnstore archival compression to further reduce the data size for situations when you can afford extra time and CPU resources to store and retrieve the data. You can configure columnstore archival compression on the following database objects: A whole columnstore table or a whole clustered columnstore index.
How does data compression help you save space?
However, you will need less I/O to read and write those tables, since they now occupy (much) less space on disk. Unless you are severely CPU-bound – in which case you have bigger problems – compression may well enhance your system throughput by reducing I/O.