Monday, November 13, 2006

sys.sp_estimated_rowsize reduction_for_vardecimal

By now you probably know that Service Pack 2 has added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits.

This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.

So what is the name of this new stored procedure? The name is sys.sp_estimated_rowsize_reduction_for_vardecimal . The proc returns the following columns: avg_rowlen_fixed_format, avg_rowlen_vardecimal_format and row_count

How do you call this proc?
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'TableName'

If your table is named OrderDetails you would call it like this
exec sys.sp_estimated_rowsize_reduction_for_vardecimal 'OrderDetails'

Read this post from the SQL Server Storage Engine team for more info

No comments: