SQL Server 2016 COMPRESS 和 DECOMPRESS 函数

SQL Server 2016 COMPRESS 和 DECOMPRESS 函数

英文原文：

https://www.simple-talk.com/blogs/2015/12/24/compresss-and-decompress-in-sql-server-2016/

 

COMPRESS和DECOMPRESS适用于SQL Server 2016和Azure SQL Database。

 

COMPRESS介绍

使用GZIP算法压缩输入表达式。压缩结果为varbinary(max)类型的字节数组。

语法

COMPRESS(expression)

参数

expression是一个nvarchar(n), nvarchar(max), varchar(n), varchar(max), varbinary(n), varbinary(max), char(n), nchar(n), or binary(n) 表达式。

注意

压缩数据不能被索引

示例

A.在插入数据到表时压缩数据

INSERT INTO player (name, surname, info )
VALUES (N'Ovidiu', N'Cracium',
COMPRESS(N'{"sport":"Tennis","age": 28,"rank":1,"points":15258, turn":17}'));
INSERT INTO player (name, surname, info )
VALUES (N'Michael', N'Raheem', compress(@info));

B.归档被删除行的压缩数据

DELETE player
WHERE datemodified < @startOfYear
OUTPUT id, name, surname datemodifier, COMPRESS(info)
INTO dbo.inactivePlayers ;

 

DECOMPRESS介绍

使用GZIP算法解压缩输入表达式。返回结果为varbinary(max)类型。

语法

DECOMPRESS(expression)

参数

expression是一个varbinary(n), varbinary(max), or binary(n)

返回类型

用户应该显式转换结果为目标类型

示例

A.在查询时解压缩数据

SELECT _id, name, surname, datemodified,
CAST(DECOMPRESS(info) AS NVARCHAR(MAX)) AS info
FROM player;

B.使用计算列显示压缩数据

CREATE TABLE (
_id int primary key identity,
name nvarchar(max),
surname nvarchar(max),
info varbinary(max),
info_json as CAST(decompress(info) as nvarchar(max))
);

 

COMPRESS和DECOMPRESS实战

SQL Server 2016的新特性可以使用COMPRESS和DECOMPRESS函数在DML（INSERT/SELECT/UPDATE）操作中压缩和解压缩列。

我们注意到压缩列不能被索引。换句话说，如果不使用该列作为搜索条件，压缩或许是一个好的选择。

让我们看一个这些函数的示例和压缩结果。

-- Create the first sample table
create table WithoutCompress
(id int identity(1,1),
longfield varchar(max) )
go

-- Optimize large values storage (see more about this here )
exec sp_tableoption 'WithoutCompress' , 'Large Value Types out of row',true
go

-- Insert a thousand of big records
insert into WithoutCompress (longfield) values (replicate('this is a compress example',1000))
go 1000

-- Check the total pages of the table
select allocation_unit_type_desc,page_type_desc, count(*) as TotalPages
from sys.dm_db_database_page_allocations (DB_ID('test'),OBJECT_ID('WithoutCompress'),0,1,'DETAILED')
group by allocation_unit_type_desc,page_type_desc
order by allocation_unit_type_desc

我们有1012个TEXT_MIX_PAGE，除了其他类型的页。

-- Create the 2nd table
create table WithCompress
(id int identity(1,1),
longfield varbinary(max) )
go

压缩信息的列为varbinary(max)

-- Optimize large values storage
exec sp_tableoption 'WithCompress' , 'Large Value Types out of row',true
go

-- Insert a thousand large records
insert into WithCompress (longfield) values (Compress(replicate('this is a compress example',1000)))
go 1000

-- Check the total pages of the 2nd table
select allocation_unit_type_desc,page_type_desc, count(*) as TotalPages
from sys.dm_db_database_page_allocations (DB_ID('teste'),OBJECT_ID('WithCompress'),0,1,'DETAILED')
group by allocation_unit_type_desc,page_type_desc
order by allocation_unit_type_desc

现在我们只有24个TEXT_MIX_PAGE。当然这个比率依赖于表中数据。如果你的数据已经被压缩，如JPG文件，例如，这个比率不会很高，只会在压缩和解压缩过程中消耗CPU。

当我们查询表时可以解压缩信息。示例查询为：

select top 10 decompress(longfield) as longfield from WithCompress

正如上图所见，DECOMPRESS函数的结果是varbinary(max)类型。我们需要转换结果来看到原始信息。让我们添加CAST到查询中：

select top 10 cast(decompress(longfield) as varchar(max)) as longfield
from WithCompress

压缩使用GZIP算法，因此我们可以在客户端应用程序中解压缩信息，而不是查询中。varbinary列匹配完整性框架中的byte[]。我们可以使用GZipStream在.NET中解压缩信息。

这是一个解压缩列和在一个Windows表单textbox中显示结果的示例代码：

var rec = (from x in ctx.WithCompresses
select x).FirstOrDefault();
MemoryStream ms = new MemoryStream(rec.longfield);
GZipStream gz = new GZipStream(ms, CompressionMode.Decompress);
StreamReader sr = new StreamReader(gz);
textBox2.Text = sr.ReadToEnd();