Description
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).
|
Price: |
Free |
|
Current Version: |
1.0.3 |
|
Download: |
Binary Compress (this
utility has been downloaded:
195
times). |
|
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. |
|
Forum:
|
Binary Compress.
Note: Forums are for asking questions and discussing issues related to a utility.
|
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!';
.