Just the other day I received an email from a client who was confused about the Datatype Decimal. Let us learn today how it works in this quick blog.
First of all, lots of people think that Datatype Decimal and Datatype Numeric are different from each other. It is not true. Numeric is just another name of the Decimal data type.
The biggest confusion around the Decimal datatype is how the precision and scale works. For example, lots of people do not understand that precision is the total number of digitals to be stored and scale is everything after the decimal points.
If you have a decimal(4,2) as a data type, it means it can maximum store values till 99.99. Yes, it is correct. 4 stands for the total number of digits, and 2 stands for how many digits we can have after the decimal point.
In other words, if you try to store value 9.9 in the column with the decimal(4,2), it will actually store it as a 9.90 and if you try to store anything greater than 99.99, it will throw an error.
Here is the working example:
DECLARE @Var DECIMAL(4,2) SET @Var = 99.99 SELECT @Var GO
When you run the script above it will return 99.99. Now try to run the following script.
DECLARE @Var DECIMAL(4,2) SET @Var = 999.9 SELECT @Var GO
The script above will give the following error:
Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting numeric to data type numeric.
Well, I hope this explains how the datatype decimal works with precision and scale. If you have any questions, do reach out to me on Twitter.
Reference: Pinal Dave (https://blog.sqlauthority.com)
Actually, making DECIMAL(S,P) and NUMERIC(S,P) synonyms is a hangover from the old Sybase days. The way we define this in the ANSI/ISO standards was that the DECIMAL(S,P) data type could be more precise than its declaration, while the NUMERIC(S,P) data type was exactly as declared. The reason for this was when we were setting up standard we had COBOL style picture representations (NUMERIC(S,P)) and BCD representations ( DECIMAL(S,P)). I found it’s always better to use DECIMAL(S,P) in my code because I may have to ported over to another SQL implementation. I like getting the extra precision…
Thank you sir for the additional information. You always make the blog posts interesting.