Vardecimal

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

  • 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?

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

  • 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

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

  • 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

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

  • 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

  • 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?

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

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

  • 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?

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

  • [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?

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

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

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