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