توابع COMPRESS و DECOMPRESS در SQL Server 2016

• COMPRESS: این تابع داده های ورودی را با استفاده از الگوریتم GZIP فشرده کرده و نتیجه باینری شده را در قالب نوع داده VARBINARY(MAX) باز می گرداند.
• DECOMPRESS: این تابع داده های باینری و فشرده ورودی را با استفاده از الگوریتم GZIP از حالت فشرده خارج کرده و نتیجه باینری شده را در قالب نوع داده (VARBINARY(MAX باز می گرداند.

SELECT DECOMPRESS
(0x1F8B080000000000040073CECF2D284A2D2E5648CC4B5170494D8671DD4AF3924B32F3F31482037D1482538BCA528B004E16B8732B000000)

مثال هایی دیگر از توابع COMPRESS و DECOMPRESS

CREATE TABLE dbo.product
(
Id INT IDENTITY(1,1),
Name NVARCHAR(max),
Description VARBINARY(MAX)
)
GO
INSERT INTO dbo.product (Name, Description)
VALUES('TestDemo', COMPRESS(N'This Demo is to show how we can use
the new Compress and decompress function in sql server 2016 CTP 3.1 onwards'))

SELECT Id, Name, description,
CAST( DECOMPRESS(description) AS NVARCHAR(MAX)) AS DecomressedTest
FROM dbo.product

میزان فضای ذخیره سازی داده های فشرده شده

DECLARE @TextToCompress1 VARCHAR(MAX)
DECLARE @TextToCompress2 VARCHAR(MAX)
DECLARE @TextToCompress3 VARCHAR(MAX)
SELECT @TextToCompress1 = 'Data Compression SQL SERVER'
SELECT @TextToCompress2 = 'The compression technique which were available with Prior Version of
SQL server are Row level and Column Level compression.
SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression.
In this Tip we are going to explore and see how this can be beneficial.'
SELECT @TextToCompress3='The compression technique which were available with
Prior Version of SQL server are Row level and Column Level compression.
SQL Server 2016 CTP 3.1 Onwards we have newly introduced functions for Compression.
In this Tip we are going to explore and see how this can be beneficial.
The COMPRESS function compresses the data provided as the input expression
and must be invoked for each section of data to be compressed.
This is demo purposes only.'
SELECT DATALENGTH(@TextToCompress1) AS 'Before_compression-1',
DATALENGTH(COMPRESS(@TextToCompress1)) AS 'After_compression-1',
DATALENGTH(@TextToCompress2) AS 'Before_compression-2',
DATALENGTH(COMPRESS(@TextToCompress2)) AS 'After_compression-2',
DATALENGTH(@TextToCompress3) AS 'Before_compression-3',
DATALENGTH(COMPRESS(@TextToCompress3)) AS 'After_compression-3'

استفاده از حالت های مختلف CAST به همراه DECOMPRESS

 DECLARE @varcharValue NVARCHAR(MAX) = 'SQL Server 2016 Compress and Decompress function'
DECLARE @compressedValue VARBINARY(MAX)
SET @compressedValue = COMPRESS(@varcharValue)
SELECT @varcharValue OriginalValue,
CAST(DECOMPRESS(@compressedValue) AS VARCHAR(MAX)) AS Decompress1,
CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(10)) AS Decompress2,
CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(20)) AS Decompress3,
CAST(DECOMPRESS(@compressedValue) AS NVARCHAR(MAX)) AS Decompress4

فضای ذخیره سازی صرفه جویی شده و میزان کارایی به دست آمده با تابع COMPRESS

CREATE TABLE Test_uncompress (test varchar(max)) --table to hold uncompressed data
CREATE TABLE Test_compressed (test Varbinary(max)) -- table to hold compressed data
GO
-- Now insert test data into uncompressed table
INSERT INTO Test_uncompress values (replicate('DemoCompress and uncompress function',5000))
GO 200000
--Insert data into compressed table by doing compress of values stored in uncompressed table
INSERT INTO Test_compressed
SELECT compress(test)
FROM Test_uncompress