Post reply

Space usage qtn: decimal (18,2) vs decimal (9,2)

  • June 10, 2004 at 6:07 am

    #162607

    Hi,

    I'm having a debate with one of our programmers. We have some fields on a SQL Server 2k table that is loaded from our mainframe. The fields on SQL Server are setup larger than they need to be. The data type is decimal, Precision 18, scale 2. (FYI - I did not build any of this, I inherited it when hired)

    My question: Which field requires more space to logically store in the following example? (Given that SQL Server does have some variable data types)

    Field 1: decimal, Precision 18, scale 2

    Field 2: decimal, Precision 9, scale 2

    Number: 180.99

    Additionally, I'm studying for my MCDBA cert and already have the resources. I've done some searching and can't find this type of information. To be honest, I think that knowing this type of thing can be very important when working with large amounts of data. Can anyone recommend reading materials (Web or print) that properly explain SQL Server internals such as this?

    Thanks in advance,

    Chris.

    Chris.

  • June 10, 2004 at 8:31 am

    #509887

    BOL (Books Online) is as good a place as any. Look up numeric data type, fixed precision and scale.

    Precision 18 uses 9 bytes

    Precision 9 uses 5 bytes

    AFAIK the consumption is fixed and not variable like varchar, so to store 180.99 in numeric(18,2) would be 9 bytes and in numeric(9,2) would be 5 bytes.

    So to answer your question, numeric(18,2) would consume more space than numeric(9,2).

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 10, 2004 at 4:57 pm

    #509981

    Another quick way to check space usage for columns is to look at the length field in EM's table design screen.

    --------------------
    Colt 45 - the original point and click interface

Viewing 3 posts - 1 through 2 (of 2 total)

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