Well SP2 CTP for SQL Server 2005 is here. Amongst all things new and fixed bugs there's one that i wanted to see if it's any good.

That is the vardecimal storage format.

My first thought and try was:

Create table t1(col1 vardecimal)

followed by

Create table t1(col1 vardecimal(18,12))

which both failed nicely with the following message:

Column, parameter, or variable #1: Cannot find data type vardecimal.

Ok then let's try google and MSDN. Both return exactly zero (at the time of writing) explenations of how to do this.

in the above link of new stuff this is said about vardecimal:

Added new functionality in the SQL Server 2005 Enterprise Edition to provide an alternate storage format that can be used to minimize the disk space needed to store existing decimal and numeric data types. No application changes area are required to use its benefits. This new storage format, known as vardecimal storage format, stores decimal and numeric data as variable length columns and can be enabled or disabled at a table level on new or existing tables. If you have declared a decimal or numeric column with high precision but most values do not require it, you can potentially save the disk space needed to store the table. A new stored procedure is provided to estimate the reduction in average row size with the new storage format.

My next step was to find a that stored procedure. After a bit of Master DB diving i found

ALTER procedure [sys].[sp_db_vardecimal_storage_format]    
    @dbname sysname = NULL,    -- database name to change
    @vardecimal_storage_format  varchar(3) = NULL    -- vardecimal format to turn on/off
as ...

Great, but this enables the db not the table... a bit more Master DB diving turned up:

ALTER procedure [sys].[sp_tableoption]
    @TableNamePattern      nvarchar(776)
   ,@OptionName            varchar(35)
   ,@OptionValue           varchar(12)
as ...

For reduction estimation use:

ALTER procedure [sys].[sp_estimated_rowsize_reduction_for_vardecimal]  
    @table_name nvarchar (776)
as ...

So next thing to find is what option to set:

exec sys.sp_tableoption 'tableName', 'vardecimal storage format', 'on'

so how well does it do? here's some code: 

set nocount on
go
create database varDecimalON
go
create database varDecimalOFF
go
-- enable vardecimal storage format for varDecimalON Dababase
exec sp_db_vardecimal_storage_format 'varDecimalON', 'on'
go
use varDecimalON
if object_id('t1') is not null
    drop table t1
create table t1
( 

col1 decimal(18, 12),
col2 decimal(18, 12)
)
– enable vardecimal storage format on t1 in varDecimalON database
exec sys.sp_tableoption 't1', 'vardecimal storage format', 'on'
declare @i int
set @i = 1
– insert 100000 rows with 2 decimal places
while @i <= 100000
begin
insert into t1(col1, col2)
select    convert(decimal(18, 2), rand()*1000000),
convert(decimal(18, 2), rand()*1000000)
set @i = @i + 1
end
set @i = 1
– insert 100 rows with 12 decimal places
while @i <= 100
begin
insert into t1(col1, col2)
select    convert(decimal(18, 12), rand()*1000000),
convert(decimal(18, 12), rand()1000000)
set @i = @i + 1
end
go
select 'space used in varDecimalON Database'
exec sp_spaceUsed 't1', 'true'
/
– results for sp_spaceUsed
name    rows      reserved   data        index_size   unused
t1      100100    2632 KB    2592 KB     8 KB         32 KB
*/
go



use varDecimalOFF
if object_id('t1') is not null
drop table t1
create table t1
( 

col1 decimal(18, 12),
col2 decimal(18, 12)
)
declare @i int
set @i = 1
– insert 100000 rows with 2 decimal places
while @i <= 100000
begin
insert into t1(col1, col2)
select    convert(decimal(18, 2), rand()*1000000),
convert(decimal(18, 2), rand()*1000000)
<span class="kwrd">set</span> @i = @i + 1

end
– insert 100 rows with 12 decimal places
set @i = 1
while @i <= 100
begin
insert into t1(col1, col2)
select    convert(decimal(18, 12), rand()*1000000),
convert(decimal(18, 12), rand()1000000)
set @i = @i + 1
end
go
select 'space used in varDecimalOFF Database'
exec sp_spaceUsed 't1', 'true'
/
– results for sp_spaceUsed
name    rows     reserved    data        index_size   unused
t1      100100   2824 KB     2816 KB     8 KB         0 KB
*/
use master
drop database varDecimalON
drop database varDecimalOFF
set nocount off

So we see we save some space for this scenario. I've tried it also on 10 million rows for 2 decimal places and
100k for 12 decimal places  and it saved around 30 Mb. If used with smaller number of rows and
smaller decimal type the unused space is 0 when using vardecimal meaning it gets stored more compact.

So the vardecimal storage format is usefull but like everything else you have to test
it for your own system and see what it can do for you.