Vardecimal Storage Format in SP2

In case you haven’t gone through the list of all the new features included in the second service pack of SQL 2005, here is the link. These are also included in BOL if you have the latest one installed. We had covered one of the new features logon triggers in one of our blog posts before. Another interesting new addition is the new storage format – vardecimal. Oracle has had variable decimal storage instead of the fixed one since a long time – Oracle Guru Tom Kyte had answered one of the questions on his site regarding this – you can read more on that here.

Firstly, this storage format is only available in the Enterprise and Developer Edition – I do not understand why MSFT would make such a distinction for something so basic in functionality. This storage format can be usedwith the numeric and the decimal data-types and can significantly help reduce the space storage for VLDBs (Very Large DataBases). This applies at the table level since this is a storage format and not a new data-type – that means that you cannot have a mix and match – all the decimal types in a table will adhere to this variable length storage scheme. As you know, depending upon the precision (between 1 and 38) of the numeric/decimal data-type, the storage can vary from 5 to 17 bytes. With fixed storage format, regardless of the value stored in a table, the space used is the same since it is bound to the precision. This would thus mean high storage needs for large tables with lots of decimal columns. If some of those are used in indexes, it would also mean that one can have only a few enteries per index page since the pagesize is fixed. If one could have variable length storage, then the number of index pages would reduce as well for those columns that are using the decimal data-types. Less number of pages would mean not only reduced storage but also faster look-ups for range scans.

As per the BOL:

When a table is stored in the vardecimal storage format, the decimal columns of each row consume only the space that is required to contain the provided number, plus 2 bytes of overhead. The result is always between 5 and 20 bytes. This includes the 2 bytes of overhead to store the offset to the value. However, null values and zeros are treated specially and take only 2 bytes.

You need to first enable the database for the variable decimal storage format. You can use the system stored procedure “sp_db_vardecimal_storage_format” to do so. Example:

exec sp_db_vardecimal_storage_format ‘decpherdemo’, ‘ON’

So, why do we have to do this at the database level when this option is really at the table level? That is to prevent attaching a vardecimal enabled database to prior versions of SQL 2005 which do not support this feature. The above commad internally increments the version number for the database.
MSFT has also provided a stored procedure to estimate the savings in space – sp_estimated_rowsize_reduction_for_vardecimal. You can use that stored procedure to estimate the cost savings in your environment. BOL has examples on it’s execution.
If you have ascertained that you indeed need to change the storage format, then uou can use the sp_tableoption stored procedure to turn “vardecimal storage format” on or off (1 or 0 value for the actual command) at the table level. VLDB’s or large data-warehouses stand to benefit the most. In order to find out the tables that have this option enabled for them, you can use the objectproperty() function –

select *
from sys.objects
where objectproperty(object_id, N’TableHasVarDecimalStorageFormat’) = 1