With every release of SQL Server, Microsoft continues to add enhancements to the product. In this blog post, we will explore the new features for SQL Server 2016 – COMPRESS and DECOMPRESS functions.

Prior to SQL Server 2016, the available compression techniques were limited to page level and row level compression. However, with the introduction of the COMPRESS and DECOMPRESS functions, we now have the ability to specify a string that needs to be compressed and insert it directly into the database. It is important to note that the COMPRESS function outputs a byte array of VARBINARY(MAX) type. The algorithm used by these functions is GZip, a widely-used compression algorithm.

One of the advantages of using these functions is that an application can compress the data using the standard Gzip algorithm and send it to SQL Server. Alternatively, the application can select the data, compress it on the fly using the select statement, and later decompress it using the standard Gzip algorithm. This can help reduce network usage, especially for data types such as XML or JSON which can be compressed more effectively.

Let’s take a look at a quick example:

SET NOCOUNT ON
GO
USE tempdb
GO
DROP TABLE IF EXISTS Team_SQLAuthority;
GO
CREATE TABLE Team_SQLAuthority (
    id INT PRIMARY KEY IDENTITY,
    name NVARCHAR(MAX),
    surname NVARCHAR(MAX),
    info VARBINARY(MAX)
)
GO
INSERT INTO Team_SQLAuthority (
    name,
    surname,
    info
)
VALUES (
    'Pinal',
    'Dave',
    COMPRESS('I love SQL Server')
)
SELECT 
    id,
    info AS 'COMPRESSED-ed',
    CAST(DECOMPRESS(info) AS VARCHAR(MAX)) AS 'DECOMPRESS-ed'
FROM 
    Team_SQLAuthority
GO

In the above example, we are inserting compressed data into SQL Server. We can also select normal data, compress it on the fly using the select statement, and later decompress it using the standard Gzip algorithm. This can be particularly useful in scenarios where reducing network usage is a priority.

Additionally, the amount of compression achieved will depend on the type of data being compressed. For example, XML or JSON data may be compressed more effectively than other types of data.

Let’s take a look at another example to see how the length of the string affects compression:

DECLARE @STR1 VARCHAR(MAX)
DECLARE @STR2 VARCHAR(MAX)

SELECT @STR1 = 'I LOVE SQL SERVER'
SELECT @STR2 = 'I LOVE SQL SERVER AND ALL OTHER DATABASE PRODUCTS. 
LET US ADD MORE DATA TO SHOW THAT MORE
LENGTH OF THE STRING CAN SHOW BETTER COMPRESSION BECAUSE THERE 
IS AN OVERHEAD DUE TO COMPRESSION ITSELF'

SELECT  
    DATALENGTH(@STR1) AS 'Original-1',
    DATALENGTH(COMPRESS(@STR1)) AS 'Compressed-1',
    DATALENGTH(@STR2) AS 'Original-2',
    DATALENGTH(COMPRESS(@STR2)) AS 'Compressed-2'

In the above example, we compare the original length of the string with the compressed length. As we can see, longer strings tend to achieve better compression. This is because there is an overhead due to compression itself.

Overall, the COMPRESS and DECOMPRESS functions in SQL Server 2016 provide a convenient way to compress and decompress data directly within the database. This can help reduce network usage and improve performance in scenarios where data compression is beneficial. Consider leveraging these functions in your SQL Server 2016 projects to take advantage of this new feature.