SQL Server数据库的大数据量表和索引压缩

SQL Server–2008/2012 企业版，提供了数据压缩的功能，普遍情况下，采用页的压缩方式能获得5
倍的压缩效果，压缩技术虽然增加了一定CPU 运算的工作量，但它极大的减少了磁盘的空间站用，节省
了大量的磁盘IO，总体性能得以加速，如果企业的数据量每年以20G 或以上的数据量递增的性况下，采
用数据表/索引压缩特性，是解决IO 性能问题的首选。
以下以Test 表为例创建表及索引的压缩;

-- 压缩表
ALTER TABLE [dbo].[Test] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE
)

--压缩表索引
ALTER INDEX [IX_Test Name] ON [dbo].[Test ] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )

注意：数据量大的表压缩耗时很长，需要在系统停用的情况下进行压缩。

	declare @TableName nvarchar(50)
	declare @IxTemp table
	(
		index_name nvarchar(100),
		index_description nvarchar(100),
		index_keys nvarchar(100)
	)
	declare @Sql nvarchar(max)
	declare @SelectSql nvarchar(max)
	declare @IxSQL nvarchar(max)

	--定义标明
	Set @TableName='Lot'

	-------------------------压缩表-------------------------
	--设置压缩表SQL，执行
	Set @Sql='ALTER TABLE [dbo].['+@TableName+'] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE )'
	exec (@Sql)
	
	-------------------------压缩表-------------------------

	-------------------------获取表索引-------------------------
	--获取表索引并写入临时变量表
	Set @SelectSql=''
	insert into @IxTemp
	(
	    index_name ,
	    index_description ,
	    index_keys
	)
	EXEC Sp_helpindex @TableName
	-------------------------获取表索引-------------------------
	
	-------------------------压缩表索引-------------------------
	--开始处理索引数据
	declare	@index_name nvarchar(100),
					@index_description nvarchar(100),
					@index_keys nvarchar(100)

	declare Ix_Cur cursor for 
	Select * from @IxTemp
	open Ix_Cur
	fetch next from Ix_Cur  into @index_name,@index_description,@index_keys
	while @@fetch_status=0     
	begin
		Set @IxSQL=''
		--压索引
		Set @IxSQL='ALTER INDEX ['+@index_name+'] ON [dbo].['+@TableName+'] REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF,
		STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
		ONLINE = OFF, SORT_IN_TEMPDB = OFF, DATA_COMPRESSION = PAGE )'

		Select @IxSQL

		fetch next from Ix_Cur  into @index_name,@index_description,@index_keys
	end 
	close Ix_Cur
	deallocate Ix_Cur 
	-------------------------压缩表索引-------------------------