Web Listing 3: Using the VarBinaryComp UDT in T-SQL Code

USE AdventureWorks
GO
-- Declare a variable to load a document from the Document table
-- This record contains an uncompressed document, 30208 bytes in length
DECLARE @Document varbinary (max);
SELECT @Document = Document FROM [AdventureWorks].Production.Document
WHERE DocumentID = 1;

Print Datalength(@Document); --30208

-- Now Declare a variable of type VarBinaryComp.  Instantiate it using the
--	Shared (i.e. static) method ParseVarBinaryU
DECLARE @VarBC [VarBinaryComp];
SET @VarBC = VarBinaryComp::ParseVarBinaryU(@Document);

Print DataLength(@VarBC.CompressedBytes);  -- 13958
Print DataLength(@VarBC); -- 13963 (5 extra bytes to store Uncompressed Length
						  --        and Null indicator)	

-- To Get the length of the Uncompressed Contents, we can issue either of the 2 following statements
--Print DataLength(@VarBC.UnCompressedBytes); --30208
Print @VarBC.UnCompressedLength; --30208


-- If you just wanted to use the Shared Method to Compress Binary data, you
--	can do the following.
DECLARE @DocumentCompressed varbinary(max);
SET @DocumentCompressed =  VarBinaryComp::Compress(@Document);
Print DataLength(@DocumentCompressed);  -- 13958


-- Sometimes, you might want your application to handle the compression for you.
--  In this case, you can use the Shared Method ParseVarBinaryC to instantiate
--	a VarBinaryComp variable with data that is already compressed
SELECT @Document = @VarBC.CompressedBytes;

Print Datalength(@Document); --13958
-- Typically, we would want the application to pass in the UnCompressed Length
-- along with the Compressed Bytes.  But, I have added a bit of logic to the
-- ParseVarBinaryC method to temporarily decompress and assess length
-- if the Length value is set to 0
SET @VarBC = VarBinaryComp::ParseVarBinaryC(@Document, 0);
Print Datalength(@VarBC);  --13963 (again, 5 extra bytes for storing length/null indicator)
Print @VarBC.UnCompressedLength; -- 30208

Print 'Done.';