Binary Compress

Implemented as a set of CLR user-defined functions, Binary Compress lets you compress/decompress large binary data in SQL Server 2005/2008. Note that SQL Server 2008, which supports built-in row/page compression in the Enterprise Edition, does not provide any binary compression capabilities.

I originally wrote about these functions in the March 2006 edition of SQL Server Magazine – see http://www.sqlmag.com/Article/ArticleID/49065/sql_server_49065.html. I encourage you to read this article to learn more about the implementation details. A key enhancment I’ve added to this version is the ability to both compress and encrypt large binary data.

Additionally, I wrote a follow up article (June 2008 – http://www.sqlmag.com/Article/ArticleID/98305/sql_server_98305.html) that takes this logic and packages it into a large CLR-UDT (large UDTs were introduced in SQL Server 2008).

Download Notes: Download and extract the project files to your computer; the compiled DLL is included in the /bin directory (source is included). Deployment instructions can be found in the /Test Scripts directory.

Example Code:

These functions are called from T-SQL code (scripts, stored procedures, etc.).
Here is a sampleT-SQL script:


— Select the First Record from the Production.Document Table

DECLARE @Document varbinary (max);

SELECT  @Document = Document FROM [AdventureWorks].Production.Document WHERE DocumentID
= 1;

— Print out the Length of the Document

Print Datalength(@Document);


— Now, compress The Document

DECLARE @DocumentCompressed varbinary (max);

SELECT
@DocumentCompressed = [dbo].[CompressBytes] (@Document, 1);

— Print out the length
of the Compressed Document

Print Datalength(@DocumentCompressed);

— Finally, decompress
the document back to its original state

SELECT @Document = [dbo].[DeCompressBytes]
(@DocumentCompressed, 1);

— Print out the Length of the Document – it should match
the original length

Print Datalength(@Document);

— Now, Compress and Encrypt the
Document

SELECT @DocumentCompressed = [dbo].[CompressBytesE] (@Document, 1);


Print out the length of the Compressed/Encrypted Document

Print Datalength(@DocumentCompressed);


— Decrypt/Decompress the document back to its original state

SELECT @Document =
[dbo].[DeCompressBytesE] (@DocumentCompressed, 1);

— Print out the Length of the Document – it should match
the original length

Print Datalength(@Document);

Print
‘Finished!’;