Post reply

Need input on changing the size of a decimal field

  • February 13, 2009 at 6:15 am

    #126382

    Hello Everyone,

    I have a decimal field I'd like to change the size of. In Enterprise Manager the field is listed as "decimal 5(6,2)" I am wanting to change the size from 5 to 7. I have been successful changing the precision and the scale by using this script in Query Analyzer...

    ALTER TABLE table_name

    ALTER COLUMN column_name DECIMAL (p,s)

    Any thought no how I may accomplish this?

    Thank you in advance for your assistance.

  • February 13, 2009 at 6:54 am

    #943531

    If I'm understanding your question correctly, the 5 is the number of storage bytes which is determined by your precision. You have no control over it. Check out BOL.

  • February 13, 2009 at 7:17 am

    #943560

    Thank you JTS,

    That did the trick - it now reads "decimal 9(10,2)"

    Again Thanks.

    Later - SgtSnafu

  • February 13, 2009 at 11:01 am

    #943814

    SgtSnafu,

    May I ask why you are concerned about the amount of storage bytes? You really should just be looking at what precision and scale your data requires and let that dictate your requirements. To say that you want the amount of storage bytes to be a particular number seems a little strange to me. :unsure:

  • February 13, 2009 at 2:04 pm

    #943925

    Hey JTS,

    Now that I think about it, this is going to sound stupid..:blush: I was originally of the impression that the size in bytes coincided with the number of digits the field could hold, such that 685.45 would be a size of five bytes, I needed the space to hold seven... I have since come to realize the program we are using to create the SQL Data was at fault - the programmer hard coded the field program to hold xxx.xx... Originally I was led to believe it was a database limitation, so I equated the 5 total digits with the 5 I found as the byte size...

    But thank you for your input, I learnt something today, and that makes it a good day..

    Later - SgtSnafu

  • February 13, 2009 at 2:37 pm

    #943941

    Glad I could help and I don't think that was a stupid assumption on your part. Keep learning and soon you'll be an expert. Someday I might be one too. 😀

  • February 13, 2009 at 7:45 pm

    #944012

    Just a note... if I need a decimal of a certain precision and scale, I always lookup the number of storage bytes for that precision and go for the highest precision I can get out of that block of bytes. It doesn't cost anymore, byte wise or performance wise and it sometimes keeps me from having to make "upgrades" later on.

    --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 7 posts - 1 through 6 (of 6 total)

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