Vardecimal

Question

Vardecimal

Don Cooper-437855

Old Hand

Points: 385
More actions
March 18, 2008 at 7:23 am

#188318

Does 2005's (sp2) vardecimal option have any affect on "money" type fields?

Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

The only difference I can see between Decimal and Money data types is that Money has a fixed four digits after the decimal point, whereas the "regular" decimal is configurable. Otherwise I think they are fully "compatible" types.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

VARDECIMAL OPTION? Where did you find that? I can't even find it in Books Online... Is it a database setting or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 3

Don Cooper (3/18/2008)
Does 2005's (sp2) vardecimal option have any affect on "money" type fields?

No, it only affects the numeric and the decimal datatypes.

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Heh... well I'll be... I found it...

http://msdn2.microsoft.com/en-us/library/bb326653.aspx

... learn something new every day...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

Jeff Moden (3/18/2008)
VARDECIMAL OPTION? Where did you find that? I can't even find it in Books Online... Is it a database setting or ???

Hi Jeff,

it was introduced in SP2, more info on it in http://msdn2.microsoft.com/en-us/library/bb326653.aspx

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Don Cooper-437855 Old Hand Points: 385 More actions · Answer 6

I initially found reference to it at http://www.sqlcommunity.com/default.aspx?tabid=77&id=211. Additional information was found using the Help / Search w/in SQL Management Studio.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

Don Cooper (3/18/2008)
I initially found reference to it at http://www.sqlcommunity.com/default.aspx?tabid=77&id=211. Additional information was found using the Help / Search w/in SQL Management Studio.

Do note that this is an Enterprise/Developer edition feature 🙂

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 8

Wow - I didn't even read the question correctly. Thanks Jeff for the wake-up there (pinging the caffeine IV).

I just came across this -

http://weblogs.sqlteam.com/mladenp/archive/2006/11/10/19546.aspx

It does seem to work as described...hmm

Update #2:

I'm delusional - it ONLY affects decimal it seems (at least as of CU6).

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 9

Matt Miller (3/18/2008)
Wow - I didn't even read the question correctly. Thanks Jeff for the wake-up there (pinging the caffeine IV).
I just came across this -
http://weblogs.sqlteam.com/mladenp/archive/2006/11/10/19546.aspx
It does seem to work as described...hmm

Heh... just getting ready to hang my second bag, Matt. 😀

I found an even better reference... shows the answer to the OP's original question and has a great explanation of what it is, problems you may face with replication (no so bad), and that it only works on Enterprise and Developer Editions. They also explain why you might want to use it (save disk space)... that might help folks that can only afford the Standard Edition more... wish they'd include it there, too.

http://msdn2.microsoft.com/en-us/library/bb326755.aspx

Anybody know if it happens to fall into an ANSI standard, anywhere?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

Gosh... ya gotta wonder, though... seems like the VarDecimal datatype option has a pretty limited application... you'd think they'd have spent more time on something important like getting SUM() OVER() to work correctly or making an ICON to hide the results window or adding a CONNECT BY or making a highspeed machine language level Tally function or a Running Total function or...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 11

Jeff Moden (3/18/2008)
Gosh... ya gotta wonder, though... seems like the VarDecimal datatype option has a pretty limited application... you'd think they'd have spent more time on something important like getting SUM() OVER() to work correctly or making an ICON to hide the results window or adding a CONNECT BY or making a highspeed machine language level Tally function or a Running Total function or...

Shh....shhh.... It's all right....hehe :w00t:

Get the second/fifth cup of unleaded going.... I know we're almost there - but the easter bunny hasn't stopped by YET.:D

I guess they were going to the low-hanging fruit. Considering you have to use CAST (see my updated post above) - I'm really not so sure how useful it is as well. But hey - I suppose if you're down to looking to say 80MB / decimal column on 10M rows, it's useful. Does this now have the same gotchas as CHAR vs. varchar? meaning - if it's close, don't bother?

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 12

But I want my Easter Eggs NOW!!! Where's my porkchops? 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 13

[font="Arial Black"]Edit: I AM missing something obvious - this test is flawed (and will be left here only for the sake of the conversation)[/font] See response below (yes - I know - I'm talking to myself now).

Hmm...update #2. It's very strange behavior. You don't get the same gains if you store as money versus decimal. It's like it doesn't at all acknowledge that they're compatible types.

Test case #1: Inserting into Money datatype

drop table bobvar

drop table bobfixed

go

create table bobvar(id int identity(1,1), b1 money,b2 decimal(18,8))

go

create table bobfixed(id int identity(1,1), b1 money,b2 decimal(18,8))

go

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

go

insert bobvar(B1)

select top 2000000

cast(rand(row_number() over(order by sc1.object_id))*200 as decimal(18,2))

FROM sys.all_columns sc1, sys.all_columns sc2

insert bobfixed(B1)

select top 2000000

cast(rand(row_number() over(order by sc1.object_id))*200 as decimal(18,2))

FROM sys.all_columns sc1, sys.all_columns sc2

Disk usage by table results:

name Reserved (KB) data(kb)

bobfixed 59,528 59,480

bobvar 41,608 41,560

Impressive - that's a 31% decrease

Now - simply by switching it to inserting into the decimal field:

drop table bobvar

drop table bobfixed

go

create table bobvar(id int identity(1,1), b1 money,b2 decimal(18,8))

go

create table bobfixed(id int identity(1,1), b1 money,b2 decimal(18,8))

go

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

go

insert bobvar(B2)

select top 2000000

cast(rand(row_number() over(order by sc1.object_id))*200 as decimal(18,2))

FROM sys.all_columns sc1, sys.all_columns sc2

insert bobfixed(B2)

select top 2000000

cast(rand(row_number() over(order by sc1.object_id))*200 as decimal(18,2))

FROM sys.all_columns sc1, sys.all_columns sc2

Disk usage by table results:

name Reserved (KB) data(kb)

bobfixed 59,528 59,480 --exactly the same results as before as you'd expect

bobvar 56,904 56,856

AKA a 4.5% space saving. Is something strange there or am I missing something?

[font="Arial Black"]Edit: I AM missing something obvious - this test is flawed (and will be left here only for the sake of the conversation)[/font]

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 14

I think you may have a misunderstanding of what it does... VarDecimal has nothing to do with the MONEY datatype. The only time that VarDecimal saves space is if you have a lot of "0" data or a lot of integer-like data according to what I read...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)