Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Saving storage space alternative for rarely used decimal columns for SQL Server Tables


By:   |   Updated: 2016-07-04   |   Comments   |   Related: More > 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.


Last Updated: 2016-07-04


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools