Sql-server – SQL Server 2016 compress function details – external decompression

azure-data-factorycompressionsnowflakesql-server-2016

According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox – you put the data in and after some magic it gets compressed or decompressed. The problem is that I need to find a way on how to decompress this data outside SQL Server – using Snowflake or Azure Data Factory preferably. Does anyone please have a clue on how to approach this topic? This difficult in particular due to lack of detailed docs on the compression method/algorithm used.

Best Answer

According to SQL Server 2016 docs COMPRESS and DECOMPRESS methods are just a blackbox

I don't know what documentation page you are referring to but the COMPRESS reference states:

This function compresses the input expression, using the GZIP algorithm. The function returns a byte array of type varbinary(max).

You can then decompress using a GZip library in application code. PowerShell example, using .NET objects:

$connection = New-Object System.Data.SqlClient.SqlConnection("Data Source=.;Integrated Security=SSPI;Initial Catalog=tempdb")
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand("SELECT COMPRESS('this is gzip compressed data') AS GzipData;", $connection)
$gzipBytes = $command.ExecuteScalar()
$connection.Close()

$memoryStream = New-Object IO.MemoryStream
$memoryStream.Write($gzipBytes, 0, $gzipBytes.Length)
[void]$memoryStream.Seek(0,0)

$gzipStream = New-Object IO.Compression.GZipStream($memoryStream, [IO.Compression.CompressionMode]::Decompress)
$streamReader = New-Object IO.StreamReader($gzipStream)
$decompressedData = $streamReader.ReadToEnd()
Write-Host $decompressedData

Related Solutions

Sql-server – Compress backup file using T-SQL

Backup compression was introduced in SQL 2008 Enterprise, and in SQL2008R2 and later, added to Standard Edition.

When creating a backup, you can specify the WITH COMPRESSION keyword, which will ensure that the database backup size is compressed to approximately a similar size as a zipped 'normal' backup file.

For SQL2005 or older, the best way really (other than using a specific tool like RedGate) is to ensure that xp_cmdshell is enabled on the instances, and then use a command line to compact using for e.g. WinRar.

I use a cmd file that looks something like this:

@echo off
Set "winrarPath=C:\Program Files\WinRAR"
"%winrarPath%\winrar.exe" a -r "ZippedBackup.zip" "BackupFile.BAK"

You can then execute this cmd file from your 2000 / 2005 instances. You can also play around with passing %1-type variables to the cmd file, if your filenames are not generic.

SQL Server 2014 – How to Compress Database

I'm not aware of a way to automatically compress new tables. The script below will compress existing tables and indexes that are not already compressed. You could schedule this periodically to compress new tables if you don't have control over the table and index create DDL.

DECLARE @SQL nvarchar(MAX) = (
    SELECT
          N'ALTER '
          + CASE
                WHEN type_desc ='HEAP' 
                THEN 'TABLE ' 
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
                ELSE 'INDEX ' 
                + QUOTENAME(name)
                + N' ON '
                + QUOTENAME(OBJECT_SCHEMA_NAME(object_id))
                + N'.'
                + QUOTENAME(OBJECT_NAME(object_id))
        END
        + N' REBUILD WITH(DATA_COMPRESSION=ROW);
'
    FROM sys.indexes
    WHERE
        OBJECTPROPERTYEX(object_id, 'IsMSShipped') = 0
        AND EXISTS(
            SELECT 1
            FROM sys.partitions
            WHERE
                partitions.object_id = indexes.object_id
                AND partitions.index_id = indexes.index_id
                AND data_compression_desc = N'NONE'
            )
    FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)');
EXEC sp_executesql @SQL;

Note that the compression feature is only available in Enterprise and Developer Editions in SQL Server 2014 and earlier versions. It is also available in Standard Edition beginning with SQL Server 2016 SP1.

Best Answer

Related Solutions

Sql-server – Compress backup file using T-SQL

SQL Server 2014 – How to Compress Database

Related Question