Using the Functions
The T-SQL code in Listing 3 loads
the assembly in SQL Server 2005 (a process called cataloging during which the
assembly is verified for security and reliability) and creates the compression
and decompression functions. With the assembly registered and the functions
created, you can compress and decompress data without having to modify your
application layers or database objects.
The sample script in Listing 4
demonstrates how you might use the compression and decompression functions in
T-SQL code. This script first creates a table and adds some values to it. The
script then runs a compression update followed by a SELECT statement that returns
the uncompressed data. Note that this script allows column-level compression
but lacks consistency check functions.
You Don't Need to Be Afraid Any Longer
With the new varbinary(max), nvarchar(max), and varchar(max) data types, you
can use SQL Server 2005's CLR to easily compress and decompress LOBs. All you
need to do is write the compression and decompression functions, load the assembly,
and create the functions. You're then ready to use those functions in your scripts.
However, for large implementations or mission-critical applications, you might
consider using third-party products, such as SQLCompress.NET (http://www.sqlcompress.net).
In these situations, third-party applications offer several advantages, including
the use of algorithms rather than the Buffer property to get the data in and
out of BLOBs (thereby avoiding the size limitation in pre-SP2 servers), verification
of compressed data to help detect data corruption, configurable memory block
sizes (which keeps memory utilization down without impacting compression performance),
and extensibility.