I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server 2005 vardecimal storage format

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) 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.

Print | posted on Friday, November 10, 2006 11:49 AM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server 2005 vardecimal storage format

Great post! Are there any drawbacks to using vardecimal?
2/23/2007 7:43 PM | Alex Bransky
Gravatar

# re: SQL Server 2005 vardecimal storage format

thanx.

the only real drawback i know of and which is logical is that if you have a database with vardecimal enabled you can't restore it to SQL SErver without SP1.
2/25/2007 10:22 AM | Mladen
Gravatar

# re: SQL Server 2005 vardecimal storage format

This feature is being removed from future versions of SQL server..http://msdn.microsoft.com/en-us/library/bb326755.aspx
8/21/2009 3:22 PM | mat
Gravatar

# re: SQL Server 2005 vardecimal storage format

It is there in MS SQL Server 2008...
12/4/2009 4:08 PM | Tapan
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET