Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_decimal_estimate_savings_for_table
	@table_id int
	,@avg_savings float output
	set @avg_savings = 0;

	-- Get the quoted two-part name
	declare @quoted_table_name nvarchar(518);
	declare @schema_id int;
	select @schema_id = schema_id from sys.tables where object_id = @table_id;
	set @quoted_table_name = quotename(schema_name(@schema_id)) + N'.' + quotename(object_name(@table_id));

	-- Generate a query to calculate savings.
	declare @sql_string nvarchar(max);

	-- ignore locking
	set @sql_string = N'set transaction isolation level read uncommitted; ';

	-- Create temp table to receive result.
	create table #savings_table(avg_compressed_decimal_size float);
	set @sql_string = @sql_string + N'insert into #savings_table select avg(cast(';

	-- Iterate through the columns for this table, generating an expression
	-- for the sum of the compressed lengths of each decimal column.
	declare dec_columns cursor for
		select quotename(name), precision from sys.columns
			where object_id = @table_id and
			(system_type_id = 106 or system_type_id = 108);
	open dec_columns;

	-- Keep track of the number of decimal columns we find, and their total fixed length.
	declare @num_dec_columns int, @fixed_decimal_size int;
	set @num_dec_columns = 0;
	set @fixed_decimal_size = 0;
	while (1 = 1)
		declare @col_name nvarchar(258);
		declare @prec int;
		fetch next from dec_columns into @col_name, @prec;
		if (@@fetch_status <> 0)

		-- Add ' + ' between addends.
		if (@num_dec_columns > 0)
			set @sql_string = @sql_string + N' + ';
		set @num_dec_columns = @num_dec_columns + 1;

		-- Add the fixed length.
		set @fixed_decimal_size = @fixed_decimal_size +
				when @prec between 1 and 9 then 5
				when @prec between 10 and 19 then 9
				when @prec between 20 and 28 then 13
				when @prec between 29 and 38 then 17

		set @sql_string = @sql_string + N'datalength(compressnumeric(' + @col_name + '))';

	close dec_columns;
	deallocate dec_columns;

	if (@num_dec_columns = 0)

	set @sql_string = @sql_string +
		N' as float)) from ' + @quoted_table_name + N' tablesample (5000 rows)';
	exec (@sql_string);
	select @avg_savings = @fixed_decimal_size - (avg_compressed_decimal_size + 2 * @num_dec_columns) from #savings_table;
	if (@avg_savings is null)
		set @avg_savings = 0;

Last revision 2008RTM
See also

  sp_estimated_rowsize_reduction_for_vardecimal (Procedure)


  Query a named instance
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash