Post reply

Numeric or Decimal Data Type in SQL 2012?

  • March 16, 2016 at 4:38 pm

    #324961

    Is there a preferred recommendation by database administrators of whether to use Decimal or Numeric data types for values that will have decimal values?

  • March 16, 2016 at 4:56 pm

    #1865755

    bsmith 63193 (3/16/2016)

    Is there a preferred recommendation by database administrators of whether to use Decimal or Numeric data types for values that will have decimal values?

    It's been my experience that it's more important to front-enders than DBAs unless the DBA is also a "Portable Code" zealot. As BOL states, they are functionally equivalent.

    What does bug the hell out of most DBAs and real database designers is when the default is used from whatever god-forsaken IDE that someone uses and it produces NUMERIC(18,0) instead of an INT, SMALLINT, or TINYINT data-type.

    --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)
    Intro to Tally Tables and Functions

  • March 16, 2016 at 6:59 pm

    #1865770

    Jeff Moden (3/16/2016)

    It's been my experience that it's more important to front-enders than DBAs unless the DBA is also a "Portable Code" zealot. As BOL states, they are functionally equivalent.

    Jeff's right - they're the same.

    What does bug the hell out of most DBAs and real database designers is when the default is used from whatever god-forsaken IDE that someone uses and it produces NUMERIC(18,0) instead of an INT, SMALLINT, or TINYINT data-type.

    He's right again - it would irritate me to no end. Those are not the same. Please - don't ever do this to your DBA.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • March 16, 2016 at 7:07 pm

    #1865771

    Thank you for the replies. Would it be recommended to use decimal over numeric because it is a wider held standard?

  • March 16, 2016 at 7:35 pm

    #1865773

    bsmith 63193 (3/16/2016)

    Thank you for the replies. Would it be recommended to use decimal over numeric because it is a wider held standard?

    The really cool part about standards is... there are so many of them. 😉 True portability is an absolute myth.

    That, not withstanding and because it makes more sense to me, I always use DECIMAL but, as a DBA, will tolerate NUMERIC provided it's not the silly stuff like NUMERIC(18,0) for all integer types.

    --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)
    Intro to Tally Tables and Functions

  • March 17, 2016 at 3:22 am

    #1865808

    bsmith 63193 (3/16/2016)

    Would it be recommended to use decimal over numeric because it is a wider held standard?

    No. In SQL they are identical data types, there's no reason to chose one over the other unless there's a guideline that your team follows that says to use one or the other for consistency purposes.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 21, 2016 at 2:49 pm

    #1866617

    The really cool part about standards is... there are so many of them.

    :laugh:

Viewing 7 posts - 1 through 6 (of 6 total)

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