SQL SERVER2008的数据压缩与备份压缩功能

 

我们先来看看Microsoft book online里给出的说明：

SQL Server 2008 支持表和索引的行压缩和页压缩。可以为以下数据库对象配置数据压缩：

 

u        存储为堆的整个表。

u        存储为聚集索引的整个表。

u        整个非聚集索引。

u        整个索引视图。

 

对于已分区表和已分区索引，可为每个分区配置压缩选项，且对象的各个分区的压缩设置不必相同。

 

表的压缩设置不自动应用于它的非聚集索引。必须单独设置每个索引。压缩功能不可用于系统表。如果表和索引是使用CREATE TABLE 和CREATE INDEX 语句创建的，则可以压缩。若要更改表、索引或分区的压缩状态，请使用ALTER TABLE 或ALTER INDEX 语句。

 

简单地说，这以功能使得SOL Server 2008允许在表、索引和分区中执行数据压缩，这样不仅可以节省磁盘空间，而且允许更多数据装入RAM中，提升查询的性能。通过测试发现，在生产环境中，新的压缩技术可以把表的大小减少到原容量的15%～5O%。可见，这一个功能在实战中还是非常有用的。SQL Server 2008中的数据压缩有两种应用方式，即行压缩和页压缩。

 

Demo:示例演示行压缩和页压缩

 

USE AdventureWorks 

GO 

--创建压缩测试表

CREATE TABLE TestCompression (col1 INT, col2 CHAR(50)) 

GO

--写入5000笔测试数据

INSERT INTO TestCompression VALUES (10, 'compression testing　text') 

GO 5000 

-- 默认不压缩时的情况

EXEC sp_spaceused TestCompression 

GO 

-- 测试启用行压缩时的情况

ALTER TABLE TestCompression 

REBUILD WITH (DATA_COMPRESSION = ROW); 

GO 

EXEC sp_spaceused TestCompression 

GO 

-- 测试启用页压缩时的情况

ALTER TABLE TestCompression 

REBUILD WITH (DATA_COMPRESSION = PAGE); 

GO 

EXEC sp_spaceused TestCompression 

GO 

-- 改回无压缩状态

ALTER TABLE TestCompression 

REBUILD WITH (DATA_COMPRESSION = NONE); 

GO 

EXEC sp_spaceused TestCompression 

GO

/*

有图有真相，上图看各种压缩对比分析

*/

 

--估算AdventureWorks 数据库中的Production.WorkOrderRouting 表在使用ROW 压缩进行压缩后的大小。

USE AdventureWorks

GO

EXEC sp_estimate_data_compression_savings 'Production', 'WorkOrderRouting', NULL, NULL, 'ROW' ;

GO               

 

下面的示例用来测试当table创建聚集索引后分别启用行压缩和页压缩的情况

 

create table testcompression  (

       part varchar(30),

       description varchar(60),

       specification varchar(60),

       constraint pk_testcompression primary key clustered

       (part asc ))

       

       declare @part varchar(30),@description varchar(60),@specification varchar(60),@cnt int

       set @cnt=1

       while @cnt<=5000

       begin

       insert into testcompression

       select left(CONVERT(varchar(256),NEWID()),30),left(CONVERT(varchar(256),NEWID()),60),left(CONVERT(varchar(256),NEWID()),60)

       set @cnt=@cnt+1

       end

       go

       --查询数据

       select * from testcompression

       go

       --带索引时默认不压缩

       sp_spaceused 'testcompression'

       --带索引行压缩

       alter table testcompression

       rebuild with (DATA_COMPRESSION = ROW)

       go

       sp_spaceused 'testcompression'

       GO

       --带索引时页压缩

       alter table testcompression

       rebuild with (DATA_COMPRESSION = PAGE)

       go

       sp_spaceused 'testcompression'

       

       /*对比分析

       name             rows      reserved  data index_size unused

       testcompression 5000       904 KB 888 KB 16 KB  0 KB--不带压缩

       testcompression 5000        656 KB 584 KB 16 KB  56 KB--行压缩

       testcompression 5000       656 KB 584 KB 16 KB  56 KB--页压缩

       

       */

 

SQL SERVER 2008不仅能够对数据库对象进行压缩，还可以在数据库备份时进行备份压缩，只要在备份时加参数COMPRESSION 即可进行备份压缩。

备份压缩示例代码：

 

BACKUP DATABASE [AdventureWorks] TO  DISK = N'C:\Backup\AdventureWorks.bak' WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10

GO

 

 

备份压缩可以大大减小数据库备份文件的大小，由于降低了备份时的I/O，所以进行备份压缩通常可以提高备份的速度。

 

备份压缩是用CPU换空间，虽然减少了存储空间大小，但是在备份和还原时将消耗更多的CPU。