Decompressing data during selecting table in SQL Server-CodePudding

I have a table for my forms. I compressed the form templates when I inserted them into the database.

DECLARE @TemplateCompressed varbinary(MAX);
SET @TemplateCompressed = COMPRESS(N'<style></style><body><h1>Application Form</h1>...');
INSERT INTO Forms (Title,Template) VALUES ('Application Form',@TemplateCompressed);

Now, I want to decompress and read the form templates.

Here is my table:

FormId	Title	Template
1	Account Opening Form	0x1F8B0800000000000400B361286...
2	Application Form	0x1F8B0800000000000400B361286...

I want decompress templates and list them as the following table:

FormId	Title	Template
1	Account Opening Form	<style>body{color:#242424;font-size:16px;...
2	Application Form	<style></style><body><h1>Application Form</h1>...

How can I obtain the second table?

CodePudding user response：

To decompress a compressed value you use Decompress(). Then of course you need to cast/convert the varbinary to a string:

declare @TemplateCompressed varbinary(max) = Compress(N'<style></style><body><h1>Application Form</h1>...');
insert into forms (Title,Template) values ('Application Form',@TemplateCompressed);

select *, Convert(nvarchar(max), Decompress(Template)) Result
from forms;

You might also what to investigate table compression which yields much better performance and compression than gzip.