What is the vardecimal data type in SQL Server? July 7, 2008Posted by fofo in Sql Server 2005.
Tags: Sql Server 2005
Databases grow rapidly every day. That is a simple fact. When we are in charge of administrating a large database, storage space is always a concern. We would like to know all these ways and techniques that will enable us to use our storage space as efficiently as possible.
A new data type was introduced in SQL Server 2005 SP2(Service Pack 2 must be installed).We can use this new data type to reduce the size of a database.
It is called vardecimal. The vardecimal data type is used to store the Numeric/Decimal(data types with fixed precision and scale) data types. The vardecimal works the same way as the varchar works for the alphanumeric data. Basically it is a form of compression.Vardecimal data type can be very useful in a situation like this.
In some cases we can have have a decimal/numeric column with a precision scale (30,4), then each value will need 17 bytes of storage so it can be stored.If in this column most of our values are in the range of 12.5 to 178.5, then we need 5-6 bytes to store them. That means we are wasting 11-12 bytes.We can apply to this column the vardecimal data type.
Please note that the vardecimal feature does not automatically turn on when SP2 is installed. we have to do it manually with the following system stored procedures. I will use in this example the Northwind database and Categories table.Please note that we have to follow this order. First we apply it to the database and next to the table.
exec sp_db_vardecimal_storage_format ‘Northwind’, ‘ON’
exec sp_tableoption ‘categories’, ‘vardecimal storage format’, 1
We can use the following system stored procedure (sp_estimated_rowsize_reduction_for_vardecimal) that tells us in detail on how much storage space will be gained if we turn on the vardecimal data.
exec sp_estimated_rowsize_reduction_for_vardecimal ‘tablename’
For further information click here
Finally vardecimal storage format is fully supported in 2008.
However SQL Server 2008 supports Row Compression that extends vardecimal storage format(Row compression is a superset of vardecimal storage format) to all fixed length data types. Some examples of fixed length data types are integer, char, and float data types. From an application’s point of view the data is still in fixed length data type. So you do not need to change your application. The SQL Server 2008 saves the data internally in variable format length.
To apply row level compression to a whole table we need to use the following statement