By: Eli Leiba | Comments | Related: > Database Configurations
Problem
In your SQL Server database you have a lot of very large tables that use decimal data types. The values stored in those columns are not using the precision allowed and you think a lot of space may be wasted. Read this tip to learn how to reclaim this space in your SQL Server database.
Solution
Starting with SQL Server 2005, the Enterprise and Developer editions of SQL Server allow the use of vardecimal the data type. With the use of this data type you can save overall space in your database.
Important Notes about Vardecimal SQL Server Data Type
As mentioned, only the Enterprise or Developer editions of SQL Server support the vardecimal data type.
Converting to vardecimal is an offline operation. It must be done when the table is offline for the whole duration of the process.
Enable this option only if the amount of storage space saved is considerably large.
By enabling vardecimal storage on a table, you allow the engine to treat decimal values as variable length like variable-length strings, so expect an increase in CPU time, but a reduction in storage space.
Enabling Vardecimal on a Table
In the next steps, we will walk through the process of enabling vardecimal for a database and table.
Step 1:
First we must enable the vardecimal storage option on your application's database, at the database level. This is done by executing the sp_db_vardecimal_storage_format system stored procedure like this.
EXEC sp_db_vardecimal_storage_format 'YourDatabase', 'ON' GO
Step 2:
Estimate the number of bytes that will be saved for a given table by calling the sp_estimated_rowsize_reduction_for_vardecimal system stored procedure that determines the average row lengths before and after the option in step 1 is enabled.
EXEC sys.sp_estimated_rowsize_reduction_for_vardecimal 'Yourtable' GO
We get three results: avg_rowlen_fixed_format, avg_rowlen_vardecimal_format and row_count.
Step 3:
If the value for row_count * (avg_rowlen_fixed_format - avg_rowlen_vardecimal_format) is relatively large (several MB or more) then changing the table option 'vardecimal storage format' to 1 (enabled) can save a lot of space. This can be done as follows:
EXEC sp_tableoption 'YourTable', 'vardecimal storage format', 1 GO
Example
Let's take the Northwind example database table [Order Details] and make a copy of that table to a table called OrdDetails, then change the unitPrice column to decimal (12, 7) on the new table.
USE Northwind GO SELECT * into OrdDetails FROM "Order Details" GO ALTER table OrdDetails ALTER column unitPrice decimal (12,7) GO
Enable vardecimal for the database by running:
EXEC sp_db_vardecimal_storage_format 'Northwind', 'ON' GO
Estimate how many bytes will be saved by executing:
EXEC sys.sp_estimated_rowsize_reduction_for_vardecimal 'dbo.OrdDetails' GO
Result is:
- avg_rowlen_fixed_format = 34.5
- avg_rowlen_vardecimal_format = 30.1
- row_count = 2810
- 2810 * (34.5 - 30.1) = 12,364
Based on the formula above row_count * (avg_rowlen_fixed_format - avg_rowlen_vardecimal_format) we get a value of 12,364. According to these numbers and the above formula, only 12364 bytes will be saved, this is approximately 2 pages and a very small savings, so no action is needed.
However, if we want to enable this option the command is as follows:
EXEC sp_tableoption 'dbo.OrdDetails', 'vardecimal storage format', 1 GO
Note that the example was tested on SQL Server 2014 Developer Edition.
Next Steps
- Find tables with decimal type columns that have large precision and are rarely used.
- Apply the checks described in this tip.
- Consider changing the table option if storage savings are high.
- Check out more SQL Server system functions.
- Read about the SQL Server Data Types.
About the author
Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
