技术开发 频道

SQL Server 2008中的行压缩

【IT168技术文档】
  微软SQL Server 2008引入了行压缩功能,但这个功能仅在企业版中有效,SQL Server中的行压缩特性可以压缩数据列,使其占用最小的存储空间。

  在创建表或索引,或者修改表或索引时可以启用行压缩特性。压缩可以基于行级、页面级和备份级,本文将举例说明如何创建一个具备行压缩功能的表,以及修改一个表,使其具备行压缩功能。

  首先创建一个未开启行压缩功能的表,使用下面的SQL语句往表里插入一些数据:

  /****** Object: Table [dbo].[NoNCompressed Table]

  Script Date: 05/27/2009 02:24:23 ******/

  IF EXISTS (SELECT * FROM sys.objects

  WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table]')

  AND type in (N'U'))

  DROP TABLE [dbo].[NoNCompressed Table]

  GO

  CREATE TABLE [NoNCompressed Table]

  (id int, FName varchar(100), LName varchar(100))

  --增加10,000行

  declare @n int

  set @n=0

  while @n<=10000

  begin

  insert into [NoNCompressed Table] values

  (1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')

  set @n=@n+1

  end

  GO

  然后使用下面的SQL语句查询这个表占用的空间大小:

  EXEC sp_spaceused [NONCompressed Table]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table,30003 ,968 KB,944 KB,8 KB,16 KB

  现在我们创建一个启用了行压缩的表,使用下面的SQL语句插入相同数量的数据:

  /****** Object: Table [dbo].[Compressed Table]

  Script Date: 05/27/2009 02:24:57 ******/

  IF EXISTS (SELECT * FROM sys.objects

  WHERE object_id = OBJECT_ID(N'[dbo].[Compressed Table]')

  AND type in (N'U'))

  DROP TABLE [dbo].[Compressed Table]

  GO

  CREATE TABLE [Compressed Table]

  (id int, FName varchar(100), LName varchar(100)) with

  (Data_compression = ROW)

  declare @n int

  set @n=0

  --添加10,000行

  while @n<=10000

  begin

  insert into [Compressed Table] values

  (1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')

  set @n=@n+1

  end

  GO

  使用下面的SQL语句查询表的空间占用情况:

  EXEC sp_spaceused [Compressed Table]

  返回结果:

  name,rows,reserved,data,index_size,unused

  Compressed Table,30003 ,584 KB,576 KB,8 KB,0 KB

  结果显示压缩表比非压缩表占用的空间小得多。

  有一种情况是表中已经有很多数据,但现在需要进行压缩,该怎么办呢?在这种情况下,可以ALTER TABLE语句增加数据压缩功能,我们创建另一个未启用压缩功能的表来模拟一下,使用下面的SQL语句添加数据到表中:

  /****** Object: Table [dbo].[NoNCompressed Table] Script Date: 05/27/2009 02:24:23 ******/

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table2]') AND type in (N'U'))

  DROP TABLE [dbo].[NoNCompressed Table2]

  GO

  CREATE TABLE [NoNCompressed Table2]

  (id int, FName varchar(100), LName varchar(100))

  declare @n int

  set @n=0

  while @n<=10000

  begin

  insert into [NoNCompressed Table2] values

  (1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')

  set @n=@n+1

  end

  GO

  使用下面的SQL语句查询该表占用的空间大小:

  EXEC sp_spaceused [NONCompressed Table2]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table,30003 ,968 KB,944 KB,8 KB,16 KB

  使用下面的ALTER TABLE语句启用表的压缩功能:

  ALTER TABLE [NoNCompressed Table2]

  REBUILD WITH (DATA_COMPRESSION = ROW );

  然后使用下面的SQL语句查询表的空间占用情况:

  EXEC sp_spaceused [NONCompressed Table2]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table2,30003 ,592 KB,560 KB,8 KB,24 KB
 

  还有一种情况是你需要使用数据压缩功能创建索引,创建一个无压缩功能的表,使用下面的SQL语句插入大量的数据:

  /****** Object: Table [dbo].[NoNCompressed Table3] Script Date: 05/27/2009 02:24:23 ******/

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table3]') AND type in (N'U'))

  DROP TABLE [dbo].[NoNCompressed Table3]

  GO

  CREATE TABLE [NoNCompressed Table3]

  (id int, FName varchar(100), LName varchar(100))

  declare @n int

  set @n=0

  while @n<=10000

  begin

  insert into [NoNCompressed Table3] values

  (1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')

  set @n=@n+1

  end

  GO

  使用下面的SQL语句查询该表占用的空间大小:

  EXEC sp_spaceused [NONCompressed Table3]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table,30003 ,968 KB,944 KB,8 KB,16 KB

  使用下面的语句在表上添加一个集群索引:

  create clustered index [NoNCompressed Table3_Cl_Idx] on

  [NoNCompressed Table3](ID)

  使用下面的SQL语句再次查询该表占用的空间大小:

  EXEC sp_spaceused [NONCompressed Table3]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table3,30003 ,1256 KB,1096 KB,64 KB,96 KB

  使用下面的SQL语句在表上增加一个非集群索引:

  create Nonclustered index [NoNCompressed Table3_NonCl_Idx] on

  [NoNCompressed Table3](Fname)

  然后使用下面的SQL语句查询该表的空间占用情况:

  EXEC sp_spaceused [NONCompressed Table3]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table3,30003 ,2096 KB,1096 KB,824 KB,176 KB

  创建一个类似的无压缩功能的表,使用下面的SQL语句插入大量的数据:

  /****** Object: Table [dbo].[NoNCompressed Table4] Script Date: 05/27/2009 02:24:23 ******/

  IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table4]') AND type in (N'U'))

  DROP TABLE [dbo].[NoNCompressed Table4]

  GO

  CREATE TABLE [NoNCompressed Table4]

  (id int, FName varchar(100), LName varchar(100))

  declare @n int

  set @n=0

  while @n<=10000

  begin

  insert into [NoNCompressed Table4] values

  (1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')

  set @n=@n+1

  end

  GO

  接下来使用下面的SQL语句开启压缩,并创建一个集群索引:

  create clustered index [NoNCompressed Table4_Cl_Idx] on

  [NoNCompressed Table4](ID)

  with (data_compression = ROW)

  使用下面的SQL语句查询表的空间占用情况:

  EXEC sp_spaceused [NONCompressed Table4]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table4,30003 ,744 KB,616 KB,64 KB,64 KB

  使用下面的SQL语句开启压缩功能,并创建一个非集群索引:

  create Nonclustered index [NoNCompressed Table4_NonCl_Idx] on

  [NoNCompressed Table4](Fname)

  with (data_compression = ROW)

  使用下面的SQL语句查询表的空间占用情况:

  EXEC sp_spaceused [NONCompressed Table4]

  返回结果:

  name,rows,reserved,data,index_size,unused

  NoNCompressed Table4,30003 ,1264 KB,616 KB,496 KB,152 KB

  从[NONCompressed Table4]和 [NONCompressed Table3]的空间使用结果可以看出在索引上的行压缩效率更高。

  本文介绍了如何在表和索引上开启行数据压缩功能。

  原文出处:http://www.databasejournal.com/features/mssql/article.php/3822901/Row-compression-in-SQL-Server-2008.htm

  原文名:Row compression in SQL Server 2008

  作者:Columnist MAK

0
相关文章