Data Compression with SQL Server 2005 SP2 and SQL Server 2008

  • Article

One of the very useful feature introduced in SQL Server 2005 SP2 and SQL Server 2008 is vardecimal data type. When the databases are growing every day it is a significant concern for the DBAs to use the storage space efficiently. Bigger the row size then bigger the backups will be, bigger the indexes will be an so on, if the data is replicated then the requirement for the storage space grows manifolds.

With SQL Server 2005 SP2 the vardecimal data type is introduced that will be used to store the Numeric Data and that has a precision and scale of a decimal data type but stores smaller numeric values in lesser space. The vardecimal works the same way as the varchar works for the alphanumeric data.

This data type is helpful when you would like to maintain the field big enough to allow the maximum possible numeric value SQL Server supports at the same time this field stores relatively small data most of the time. For Example: A bank has to define the transaction amount field as the biggest possible value but most of the time the data stored in this field is much smaller so whatever the data stored in this field SQL Server will use 17 bytes for each row.

With the vardecimal field the storage space can significantly be reduced. For the details on the space usage by vardecimal and decimal data type please refer the following table:

image

This data type will be highly helpful when most of the rows will contain smaller values. If the rows will contain the maximum values of the precision 38 the it is better to use the decimal data type.

The vardecimal feature will NOT be automatically turned on when SP2 is installed but rather it will have to be manually turned on. The vardecimal feature can not be turned on for the Master Database.

HOW TO TURN ON THE VARDECIMAL OPTION

Step 1:   *exec sp_db_vardecimal_storage_format ‘<dbname>', 'ON‘ *
This option will enable the vardecimal storage on the specified database.

Step 2:  exec sp_tableoption ‘<table>', 'vardecimal storage format', 1
This option will turn on the vardecimal storage for the existing table.

HOW TO ESTIMATE WHETHER IT IS WORTH TURNING ON THIS OPTION

sys.sp_estimated_rowsize_reduction_for_vardecimal  ‘<table>‘
This option will provide with the detailed estimate on how much storage space could be gained if we turn on the vardecimal data type.

For more information please check out this WHITEPAPER.