{"id":8630,"date":"2015-12-24T02:26:29","date_gmt":"2015-12-24T02:26:29","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/compresss-and-decompress-in-sql-server-2016\/"},"modified":"2016-07-28T10:57:29","modified_gmt":"2016-07-28T10:57:29","slug":"compresss-and-decompress-in-sql-server-2016","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/compresss-and-decompress-in-sql-server-2016\/","title":{"rendered":"Compress and Decompress in SQL Server 2016"},"content":{"rendered":"\n

SQL Server 2016 ctp 3.2 is already available<\/a> with a lot of news.<\/p>\n

One of the new features available is the ability to compress and decompress fields during DML (insert\/select\/update) using the functions Compress and Decompress.<\/p>\n

One important point to notice is that compressed fields cannot be indexed. On the other hand, if you will not search records by this field, may be the compression can be a good option.<\/p>\n

Let’s see an example of how these functions work and the result of the compression.<\/p>\n

-- Create the first sample table\ncreate table WithoutCompress\n(id int identity(1,1),\nlongfield varchar(max) )\ngo<\/pre>\n<\/p>\n
-- Optimize large values storage (see more about this here )\nexec sp_tableoption 'WithoutCompress' , 'Large Value Types out of row',true\ngo<\/pre>\n
-- Insert a thousand of big records\ninsert into WithoutCompress (longfield) values (replicate('this is a compress example',1000))\ngo 1000<\/pre>\n
-- Check the total pages of the table \nselect allocation_unit_type_desc,page_type_desc, count(*) as TotalPages \nfrom sys.dm_db_database_page_allocations (DB_ID('test'),OBJECT_ID('WithoutCompress'),0,1,'DETAILED') \ngroup by allocation_unit_type_desc,page_type_desc \norder by allocation_unit_type_desc<\/pre>\n
\"compress1.png\"<\/a><\/p>\n
We have 1012 Text_Mix_Page, besides the other types of pages.<\/p>\n
-- Create the 2nd table \ncreate table WithCompress\n (id int identity(1,1), \nlongfield varbinary(max) )\ngo<\/pre>\n
The field with compressed information needs to be varbinary.<\/p>\n
-- Optimize large values storage\nexec sp_tableoption 'WithCompress' , 'Large Value Types out of row',true\ngo<\/pre>\n
-- Insert a thousand large records \ninsert into WithCompress (longfield) values (Compress(replicate('this is a compress example',1000))) \ngo 1000<\/pre>\n
-- Check the total pages of the 2nd table \nselect allocation_unit_type_desc,page_type_desc, count(*) as TotalPages \nfrom sys.dm_db_database_page_allocations (DB_ID('teste'),OBJECT_ID('WithCompress'),0,1,'DETAILED') \ngroup by allocation_unit_type_desc,page_type_desc \norder by allocation_unit_type_desc<\/pre>\n
\"compress2.png\"<\/a><\/p>\n
Now we have only 24 TEXT_MIX_PAGEs. Of course this ratio depends on the data in the table. If you have data already compressed, like jpg files, for example, the ratio won’t be so high and you will only spend cpu in the compress\/decompress process.<\/p>\n
We can decompress the information when we query the table. This is a sample query:<\/p>\n
select top 10 decompress(longfield) as longfield from WithCompress<\/pre>\n
\"compress3.png\"<\/a><\/p>\n
As you can see in the image above, the result of the decompress function is of type varbinary. We need to cast the result to see the original information. Let’s add the cast to the query:<\/p>\n
select top 10 cast(decompress(longfield) as varchar(max)) as longfield \nfrom WithCompress<\/pre>\n
\"compress4.png\"<\/a><\/p>\n
 The compression uses GZIP algorithm, so we can decompress the information in the client application, instead the query. The varbinary field is mapped to byte[] by entity framework. We can use GZipStream to decompress the information in .NET.<\/p>\n
This is a sample code to decompress the field and display the result in a windows forms textbox:<\/p>\n
var rec = (from x in ctx.WithCompresses\n             select x).FirstOrDefault();\nMemoryStream ms = new MemoryStream(rec.longfield);\nGZipStream gz = new GZipStream(ms, CompressionMode.Decompress);\nStreamReader sr = new StreamReader(gz);\ntextBox2.Text = sr.ReadToEnd();<\/pre>\n","protected":false},"excerpt":{"rendered":"
SQL Server 2016 ctp 3.2 is already available with a lot of news. One of the new features available is the ability to compress and decompress fields during DML (insert\/select\/update) using the functions Compress and Decompress. One important point to notice is that compressed fields cannot be indexed. On the other hand, if you will……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-8630","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=8630"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8630\/revisions"}],"predecessor-version":[{"id":42525,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/8630\/revisions\/42525"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=8630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=8630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=8630"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=8630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}