Just like Varchar for Char, why don’t we have Varnumeric for Numeric…

When tables grow to millions of rows reducing the amount of storage a few bytes per row can have significant effect. The decimal and numeric data types are typically stored on disk as fixed-length data. Mostly these values are defined with high precision, but precision is not required in most of these cases. In such cases the storage space consumed is unnecessary. The situation becomes even worse when NULL or zero values are also present in numeric or decimal type column. Such issues are required to be addressed for optimization of storage space, but what options are available?

SQL Server 2005 has introduced the VARDECIMAL storage format. This new storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at the table level.

When a table is not using the VARDECIMAL format every numeric or decimal value uses a fixed size (between 5 and 17 bytes) even when the data is 0. With VARDECIMAL, storage becomes dynamic and optimized for each value.

The VARDECIMAL is implemented at table level, so we can have table first with all column defined and inserted data, there after we can find how much space it takes without actually going for VARDECIMAL and how much we can save if we go for VARDECIMAL.

Let create the scenario to test the VARDECIMAL, we are creating table with decimal and numeric datatype fields

create table testVarDecimal

(col1 decimal(18,0),

col2 numeric(18,0))

Command(s) completed successfully.

Now lets try to insert around 4000 record in by the below script.

declare @cnt int

set @cnt=1

while (@cnt < 4000)

begin

      if (@cnt < 2000)

            insert into testVarDecimal values(@cnt,0)

      else

            insert into testVarDecimal values(@cnt,(@cnt + 100))

      set @cnt +=1

end

Now since we have records in place, lets analyze how much space we can reduce if we implement VARDECIMAL to this table.

exec sp_estimated_rowsize_reduction_for_vardecimal 'testVarDecimal'

avg_rowlen_fixed_format   avg_rowlen_vardecimal_format  row_count

-----------------------   ----------------------------  ----------

25.00                                   16.27           3999

We can see that there may be difference of about (25.00 – 16.27) i.e.9 bytes in each row. So it seems suitable to apply vardecimal storage for this table.

To implement VARDECIMAL, we first need to enable it for the database, which can done like the way below

execute sp_db_vardecimal_storage_format 'testDemo', 'on'

Command(s) completed successfully.

Here my database is testDemo, you can put your own database name, where your table object reside.

Now let’s implement it on table by following script

exec sys.sp_tableoption 'testVarDecimal', 'VarDecimal storage format',1

So it is all over. If you want to check which all database has VarDecimal Enabled just fire the below command

exec sp_db_vardecimal_storage_format

To check which table has VARDECIMAL enabled execute the following

select name, OBJECT_ID, type_desc from sys.objects 

WHERE OBJECTPROPERTY(OBJECT_ID, N'TableHasVarDecimalStorageFormat') = 1

Code not always straightforward, but rewards are worth it