Thursday, 20 March 2008

SQL Server 2005 vardecimal storage format

The vardecimal storage format. My first thought and try was:

Create table t1 (col1 vardecimal)

Followed by

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

These 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) explanations 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 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)

set @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.

kick it on DotNetKicks.com

No comments:

Post a Comment