Numeric or Decimal Data Type in SQL 2012?

Question

Numeric or Decimal Data Type in SQL 2012?

SilhouetteBS

SSCommitted

Points: 1597
More actions
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?

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

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

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

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)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

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

SilhouetteBS SSCommitted Points: 1597 More actions · Answer 3

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

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

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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

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

Jon.Morisi SSChampion Points: 12846 More actions · Answer 6

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

:laugh: