Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_decimal_estimate_savings_for_table

  No additional text.


Syntax
create procedure sys.sp_decimal_estimate_savings_for_table
	@table_id int
	,@avg_savings float output
AS
BEGIN
	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)
	begin
		declare @col_name nvarchar(258);
		declare @prec int;
		
		fetch next from dec_columns into @col_name, @prec;
		if (@@fetch_status <> 0)
			break;

		-- 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 +
			case
				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
			end;

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

	close dec_columns;
	deallocate dec_columns;

	if (@num_dec_columns = 0)
		return;

	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;
end;

 
Last revision 2008RTM
See also

  sp_estimated_rowsize_reduction_for_vardecimal (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
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
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash