大家好，又见面了，我是你们的朋友全栈君。
SQL SERVER 2008 及以上提供数据库表压缩功能
1. 压缩分类和比率
压缩分为行(ROW)压缩和页(PAGE)压缩,页压缩的压缩比率更高一些，正常来说一般数据库可以实现50%以上的压缩比率。
2.对性能影响
压缩后对磁盘的压力会减少，但是会增加CPU的压力，对性能的影响需要看机器的具体配置，在实际中情况中，经常是CPU没满但是磁盘已满负荷了。对备份恢复等极为有利，一个大数据库，全备份常常要3-4小时，压缩后，全备时间缩减到2小时。
以下代码生成全库的压缩脚本，注意只是生成脚本，将生成的脚本贴到SQL执行窗口中执行即可。
代码语言：javascript复制/*SQL Server 2008 以上，自动进行表压缩 直接运行生成的脚本文件即可 */
代码语言：javascript复制SET NOCOUNT ON CREATE TABLE #Temp ( table_name NVARCHAR(1000), index_name NVARCHAR(1000), table_size decimal(19,2) )
代码语言：javascript复制CREATE TABLE #tablespaceinfo ( nameinfo VARCHAR(500) , rowsinfo BIGINT , reserved VARCHAR(20) , datainfo VARCHAR(20) , index_size VARCHAR(20) , unused VARCHAR(20) )
代码语言：javascript复制INSERT #Temp(table_name,index_name) SELECT DISTINCT ‘[‘ SCHEMA_NAME(schema_id) ’].[‘ a.name ’]’, ‘[‘ c.name ’]’ FROM sys.tables a INNER JOIN sys.partitions b ON a.object_id=b.object_id AND b.data_compression=0 INNER JOIN sys.indexes c ON a.object_id=c.object_id AND b.index_id=c.index_id WHERE a.type=’U’ AND SCHEMA_NAME(schema_id)!=’cdc’
代码语言：javascript复制DECLARE @l_tableName NVARCHAR(max) WHILE EXISTS(SELECT * FROM #Temp WHERE table_size IS NULL) BEGIN SELECT TOP 1 @l_tableName=table_name FROM #Temp WHERE table_size IS NULL
代码语言：javascript复制TRUNCATE TABLE #tablespaceinfo INSERT #tablespaceinfo EXEC sp_spaceused @l_tableName
代码语言：javascript复制UPDATE #Temp SET table_size=(SELECT CAST(REPLACE(reserved, ‘KB’, ”) AS INT)*1.0/1024/1024 FROM #tablespaceinfo) WHERE table_name=@l_tableName
代码语言：javascript复制END
代码语言：javascript复制–如果要查看压缩项目， –SELECT * FROM #Temp –ORDER BY table_size ASC
代码语言：javascript复制DECLARE @tablename NVARCHAR(255); DECLARE @indexname NVARCHAR(255) DECLARE @tablesize decimal(19,2) DECLARE @sql NVARCHAR(MAX) DECLARE @message NVARCHAR(MAX) DECLARE Info_cursor CURSOR FOR SELECT table_name,index_name,table_size FROM #Temp ORDER BY table_size ASC
代码语言：javascript复制OPEN Info_cursor FETCH NEXT FROM Info_cursor INTO @tablename ,@indexname,@tablesize
WHILE @@FETCH_STATUS = 0 BEGIN
代码语言：javascript复制–ALTER INDEX [MF_NVChange_ID] ON [dbo].[MF_NVChange] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE) IF @indexname IS NOT NULL BEGIN SET @sql =’ALTER INDEX ‘ @indexname ‘ ON ‘ @tablename ‘ REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’ SET @message=’RAISERROR(”’ @indexname ’ ON ‘ @tablename ’ 已完成压缩 原空间=’ CAST(@tablesize AS nvarchar(30)) ’G”,9,1) WITH NOWAIT’ END ELSE BEGIN SET @sql =’ALTER TABLE ‘ @tablename ‘ REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)’ SET @message=’RAISERROR(”’ @tablename ’ 已完成压缩 原空间=’ CAST(@tablesize AS nvarchar(30)) ’G”,9,1) WITH NOWAIT’ END
代码语言：javascript复制PRINT @sql PRINT @message –RAISERROR( @message,9,1)
代码语言：javascript复制FETCH NEXT FROM Info_cursor INTO @tablename ,@indexname ,@tablesize END
CLOSE Info_cursor DEALLOCATE Info_cursor
代码语言：javascript复制DROP TABLE #Temp DROP TABLE #tablespaceinfo
代码语言：javascript复制生成的脚本类似如下,直接黏贴到窗口中执行即可
代码语言：javascript复制ALTER TABLE [dbo].[AAAAAAAAAAA] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
RAISERROR('[dbo].[AAAAAAAAAAA] 已完成压缩 原空间=0.00G',9,1) WITH NOWAIT发布者：全栈程序员栈长，转载请注明出处：https://javaforall.cn/166637.html原文链接：https://javaforall.cn
