difference between numeric and decimal

Question

Post reply

difference between numeric and decimal

samirca007

SSC-Addicted

Points: 417
More actions
January 6, 2020 at 10:45 am

#3712599

Hello, beginner in Tsql
I need help: here in one of the tables that I use, one of the fields is declared in decimal (10.5).
I understand that the field is therefore decimal but what are the 10 and 5 used for ??
Thanks for your help

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Have you read the documentation, and was there anything in particular you didn't understand?

John

Thom A SSC Guru Points: 99126 More actions · Answer 2

Also, it's not decimal (10.5) it's decimal(10,5), you need a comma(,) between the precision and scale.

The document that John linked to should tell you all you need, but if there are any parts you don't understand do reply, and quote the bits you're not sure on.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Lowell SSC Guru Points: 323504 More actions · Answer 3

for a definition of decimal(10,5):

10 = maximum number of digits.

5 = number of digits to the right of the decimal point.

that means the largest value allowed before an error would be 99999.99999

so less than 100K. you have to take into consideration what the max value for a column might be when you create the field, as well as when you add fields together for SUM() functions and stuff like that.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Thom A SSC Guru Points: 99126 More actions · Answer 4

Lowell wrote:

so less than 100K. you have to take into consideration what the max value for a column might be when you create the field, as well as when you add fields together for SUM() functions and stuff like that.

Worth nothing that when using SUM, the precision returned will always be 38. This means that truncation/overflow problems are only likely to occur if the scale has a value close to 38, and the values therefore exceed the maximum precision. Summing the values 99999.99999 and 1 won't generate an overflow error with SUM, for example:

SELECT SUM(I)
FROM (VALUES (CONVERT(decimal(10, 5), 99999.99999)),
             (1)) V(I);

SELECT system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT SUM(I) FROM (VALUES(CONVERT(decimal(10,5),99999.99999)),(1))V(I);', NULL, NULL);

So, if you are going to be aggregating, it's normally the scale you need to be aware of, rather than the precision.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 5

samirca007 wrote:

Hello, beginner in Tsql
I need help: here in one of the tables that I use, one of the fields is declared in decimal (10.5).
I understand that the field is therefore decimal but what are the 10 and 5 used for ??
Thanks for your help

Ummm... according to your previous posts, you've been working with SQL Server for at least 1 year and 7 months. Hardly the newbie you claim to be. 🙁

In SQL Server, there are two places where "Help" might be stored for use with SSMS... locally (my personal preference) and online (really bad if you frequently work on the server itself but OK for local instances).

To get to either, just press the {F1} key.

If things weren't done quite right during the installation of SQL Server, even that might not work so there's always a web search. In this case, you'd want to search for "sql server decimal 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)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

Jeff -it needed to be said, but we are f1 AND web search rolled into one......

have you been hanging out with Joe again ? or are you not taking your medication? 🙂 start shouting about cursors again!!!!

(ps - mail me the spare meds, I might need them)

MVDBA

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 7

MVDBA (Mike Vessey) wrote:

Jeff -it needed to be said, but we are f1 AND web search rolled into one......

Heh... I guess that's true in a lot of cases. You would think that someone that has been working with SQL Server for 19 months would know one or the other, though.

MVDBA (Mike Vessey) wrote:

have you been hanging out with Joe again ? or are you not taking your medication? 🙂 start shouting about cursors again!!!!
(ps - mail me the spare meds, I might need them)

Lordy, I hope I didn't come across that way. I didn't slam them about Cobol, Punched Cards, Roman Numerals, not knowing ISO standards that no one can afford to buy, question their humanity for not posting code, nor question his position in life just because he didn't know something.

As for the meds... they're mine, I tell you... ALL MINE! 😛

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