Reckoned Force

Full Service Business Data Solutions

Data Compression in SQL Server

By

We could use data compression in SQL Server to save both storage space and memory required for data cache. It also has a drawback of consuming high CPU usage to compress and decompress data. It is possible to apply data compression on tables and indexed views. There are two types of compression Row-level compression and Page-level compression.
Row-Level compression reduces storage space by converting fixed-length data types by removing the unused space as if it is a variable level length, by not storing NULL and reduce the metadata to store the data. Although it doesn’t deliver as much compression as Page-level compression it requires less amount of CPU.
I have a stored procedure to determine the percentage of compression delivered by each compression type.

--==================================================================================--
-- Author: Biz Nigatu
-- Description: calculate row and page level compression percentage gain
-- Sample Proc call: exec proc_calculate_compresson_precentage N'dbo',N'FactInternetSales'
--==================================================================================--
 
create proc proc_calculate_compresson_precentage ( @schema_name	  NVarchar(256),
						   @object_name	  NVarchar(256))
as
	DECLARE @data_compression NVarchar(120);
 
	DECLARE @rowCompression   TABLE (
			[object_name] NVarchar(256),
			[schema_name] NVarchar (256),
			[index_id]    Int,
			[partition_number] Int,
			[size_with_current_setting(KB)] Int,
			[size_with_request_compression_setting(KB)] Int,
			[sample_size_with_current_compression_setting(KB)] Int,
			[sample_size_with_requested_compression_setting(KB)] Int
	);
 
	DECLARE @pageCompression TABLE (
			[object_name] NVarchar(256),
			[schema_name] NVarchar (256),
			[index_id]    Int,
			[partition_number] Int,
			[size_with_current_setting(KB)] Int,
			[size_with_request_compression_setting(KB)] Int,
			[sample_size_with_current_compression_setting(KB)] Int,
			[sample_size_with_requested_compression_setting(KB)] Int
	);
 
	--check for row level compression
	SET @data_compression = N'ROW'; 
	--create temp table to hold the estimated values
 
	insert into @rowCompression
	exec sp_estimate_data_compression_savings @schema_name,
						  @object_name,
						  NULL,
						  NULL,
						  @data_compression;
	--check for page level compression
	SET @data_compression = N'PAGE';
	--create temp table to hold the estimated values
 
 
	insert into @pageCompression
	exec sp_estimate_data_compression_savings @schema_name,
						  @object_name,
						  NULL,
						  NULL,
						  @data_compression;
 
	--summarize the result of the estimation
	select 'ROW' AS compression_type,
		   [object_name],
		   [schema_name],
		   sum([size_with_current_setting(KB)]) AS [size_with_current_setting(KB)],
		   sum([size_with_request_compression_setting(KB)])  
                       AS [size_with_request_compression_setting(KB)],
		   100 -(cast(sum([size_with_request_compression_setting(KB)] )
                                 as numeric(10,2)) * 100/ 
				 sum([size_with_current_setting(KB)]))  as percentage_compression
	from @rowCompression
	group by [object_name],
			 [schema_name];
 
	select 'PAGE' AS compression_type,
		   [object_name],
		   [schema_name],
		   sum([size_with_current_setting(KB)]) AS [size_with_current_setting(KB)],
		   sum([size_with_request_compression_setting(KB)]) 
                             AS [size_with_request_compression_setting(KB)],
		   100 -(cast(sum([size_with_request_compression_setting(KB)] )
                             as numeric(10,2)) * 100/ 
			sum([size_with_current_setting(KB)]))  as percentage_compression
	from @pageCompression
	group by [object_name],
			 [schema_name];
go

Let us execute the stored procedure on AdventureWorksDW for FactInternetSales table.

exec proc_calculate_compresson_precentage N'dbo',N'FactInternetSales

Data Compression in SQL Server
We get 46% compresson using ROW and 70% compression using PAGE compression.
Once we decided which compression type to use we could apply compression as

ALTER TABLE <shemaName>.<tableName> REBUILD WITH (DATA_COMPRESSION=<compressionType>);

You may also use the following stored procedure

--===================================================================================--
-- Author: Biz Nigatu
-- Description: Apply compression to a table
-- Sample Proc call: 
--   exec proc_apply_compresson_precentage N'dbo',N'FactInternetSales','PAGE'
--===================================================================================--
 
create proc proc_apply_compresson_precentage ( @schema_name	     NVARCHAR(256),
					       @object_name	     NVARCHAR(256),
					       @compression_type NVARCHAR(20)='ROW')
as
DECLARE @sql NVARCHAR(2000),
		@parameterDefinition Nvarchar(max);
 
	SET @sql = 'ALTER TABLE ' + LTRIM(RTRIM(@schema_name)) + '.' + 
				    LTRIM(RTRIM(@object_name)) + ' REBUILD WITH 
			(DATA_COMPRESSION=' + @compression_type +')';	
 
	EXECUTE sp_executesql @sql;
go

Reference used
Rankins, Ray, Paul Bertucci, Chris Gallelli, Alex Silverstein, and Hilary,. Cotter.Microsoft SQL Server 2012 Unleashed. N.p.: Pearson Eduction, 2014.
http://msdn.microsoft.com/en-us/library/cc280576(v=sql.110).aspx
http://msdn.microsoft.com/en-us/library/cc280449(v=sql.110).aspx