sp_estimated_rowsize_reduction_for_vardecimal (Transact-SQL)

New: 12 December 2006

Estimates the reduction in the average size of rows if enable vardecimal storage format is enabled on a table. Use this number to estimate the overall reduction in the size of the table. Because the statistical sampling is used to compute the average reduction in the rowsize, regard it as an estimate only. In rare cases, rowsize may increase after you enable the vardecimal storage format. Vardecimal storage format is available only in SQL Server 2005 Enterprise, Developer, and Evaluation editions.

Syntax

sp_estimated_rowsize_reduction_for_vardecimal [ [ @table_name = ] 'table' ] [;]

Arguments

  • [ @table= ] 'table'
    Is the three-part name of the table for which the storage format is to be changed. table is nvarchar(776).

Return Code Values

0 (success) or 1 (failure)

Result Sets

The following result set is returned to provide information about current and estimated table size.

Column name Data type Description

avg_rowlen_fixed_format

decimal (12, 2)

Represents the length of the row in fixed decimal storage format.

avg_rowlen_vardecimal_format

decimal (12, 2)

Represents average rowsize when vardecimal storage format is used.

row_count

int

Number of rows in the table.

Remarks

Use sp_estimated_rowsize_reduction_for_vardecimal to estimate the savings that result if you enable a table for vardecimal storage format. For example, if the average size of the row can be reduced by 40 percent, you can potentially reduce the size of the table by 40 percent. You might not receive a space savings depending on the fill factor and the size of the row. For example, if you have a row that is 8000 bytes long and you reduce its size by 40 percent, you can still fit only one row on a data page. There is no space savings.

If the results of running sp_estimated_rowsize_reduction_for_vardecimal indicate that the table will grow, this means that many rows in the table use almost the complete precision of the decimal data types, and the addition of the small overhead required for vardecimal storage format is greater than the savings from vardecimal storage format. In this rare case, do not enable vardecimal storage format.

If a table is enabled for vardecimal storage format, use sp_estimated_rowsize_reduction_for_vardecimal to estimate what the average size of the row would be if vardecimal storage format is disabled.

Permissions

Requires CONTROL permission on the table.

Examples

The following example estimates the rowsize reduction if the Production.WorkOrderRouting table in the AdventureWorks database is compressed.

USE AdventureWorks
GO
EXEC sp_estimated_rowsize_reduction_for_vardecimal 'Production.WorkOrderRouting' ;
GO

See Also

Reference

sp_db_vardecimal_storage_format (Transact-SQL)
sp_tableoption (Transact-SQL)
Database Engine Stored Procedures (Transact-SQL)

Other Resources

Storing Decimal Data As Variable Length

Help and Information

Getting SQL Server 2005 Assistance