53

I found a table with this a column from this data type 

numeric(18, 0)

what is that please? and why does 0 and 18 mean

I already check this question Difference between numeric, float and decimal in SQL Server but couldn't understand it.\

can I add (-10) in that column?

can I add all negative number in that column?

can I add any positive number in that column?

Update 1

This is a sample of the data I found in that column

100
263
13
2
9
4
3
3
28
15
33
16
135
50
64
60
100
500
150

Update 2 Is it possible to have - and + ?

Community
  • 1
  • 1
Agnieszka Polec
  • 1,471
  • 6
  • 20
  • 26
  • 1
    It is essentially a REALLY big int. Yes you have a negative number. No you can't have any number, it must be no larger than 18 significant digits. – Sean Lange Aug 07 '14 at 19:49
  • 1
    Here is a link to BOL datatypes. http://msdn.microsoft.com/en-us/library/ms187752.aspx – Sean Lange Aug 07 '14 at 19:50
  • @SeanLange you said that `it must be no larger than 18 significant digits` but please check the updated question, there are values less that 18 digits – Agnieszka Polec Aug 07 '14 at 19:51
  • @SeanLange that link didn't provide information, are you sure of it please? – Agnieszka Polec Aug 07 '14 at 19:53
  • @SeanLange 's comment means that the values can be 18 digits or less, but not more than 18 digits. – Tab Alleman Aug 07 '14 at 19:54
  • 5
    Someone really loves to waste space if that sample is representative of the data – UnhandledExcepSean Aug 07 '14 at 19:54
  • @SpectralGhost kindly tell me what does that mean, specially the 0 and 18 and if it is possible to have `-` and `+` – Agnieszka Polec Aug 07 '14 at 19:56
  • @AgnieszkaPolec It means that a smallint would work fine (based on the sample data provided. A smallint stores its value in 2 bytes, a numeric(18,x) stores it in 9 bytes. numeric(18,0) would allow negative #s; the range of data allowed would be - 10^18 +1 through 10^18 - 1 – UnhandledExcepSean Aug 07 '14 at 19:59

2 Answers2

86

The first value is the precision and the second is the scale, so 18,0 is essentially 18 digits with 0 digits after the decimal place. If you had 18,2 for example, you would have 18 digits, two of which would come after the decimal...

example of 18,2: 1234567890123456.12

There is no functional difference between numeric and decimal, other that the name and I think I recall that numeric came first, as in an earlier version.

And to answer, "can I add (-10) in that column?" - Yes, you can.

TylerH
  • 20,799
  • 66
  • 75
  • 101
Christian Phillips
  • 18,399
  • 8
  • 53
  • 82
  • @AgnieszkaPolec yes you can have negative values. You need to learn about BOL (books online). It is the documentation for sql server. I posted a link the datatypes page earlier. The links from there will take you to the datatype pages for each datatype. It explains them in great detail. – Sean Lange Aug 07 '14 at 20:05
  • @SeanLange thanks but I checked that link and browsed to numeric section, it was empty of description. it just has links to decimal and float. Anyway, now I understood the concept of numeric. – Agnieszka Polec Aug 07 '14 at 20:08
  • Maybe you looked at the wrong page or something because the numeric page is pretty in depth. decimal [ (p[ ,s] )] and numeric[ (p[ ,s] )] Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally equivalent to decimal. p (precision) The maximum total number of decimal digits that will be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38....too long to fit here. – Sean Lange Aug 07 '14 at 20:14
22

This page explains it pretty well.

As a numeric the allowable range that can be stored in that field is -10^38 +1 to 10^38 - 1.

The first number in parentheses is the total number of digits that will be stored. Counting both sides of the decimal. In this case 18. So you could have a number with 18 digits before the decimal 18 digits after the decimal or some combination in between.

The second number in parentheses is the total number of digits to be stored after the decimal. Since in this case the number is 0 that basically means only integers can be stored in this field.

So the range that can be stored in this particular field is -(10^18 - 1) to (10^18 - 1)

Or -999999999999999999 to 999999999999999999 Integers only

Dan
  • 10,614
  • 5
  • 24
  • 35