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.';