SQL SERVER – Datatype Decimal Explained – Datatype Numeric

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. 

Datatype decimal(precision,scale)

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.

For example,

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)