Have you ever been confused about the Decimal datatype in SQL Server? You’re not alone. In this blog post, we will explore how the Decimal datatype works and clear up any misconceptions.

First, let’s address a common misconception – the Decimal and Numeric datatypes are actually the same. Numeric is just an alternative name for the Decimal datatype.

The Decimal datatype is defined as decimal(precision, scale). The precision refers to the total number of digits that can be stored, while the scale represents the number of digits after the decimal point.

For example, if you have a column defined as decimal(4,2), it means that it can store values up to 99.99. The precision of 4 indicates that there can be a total of 4 digits, and the scale of 2 means that there can be 2 digits after the decimal point.

It’s important to note that if you try to store a value like 9.9 in a column with the decimal(4,2) datatype, it will actually be stored as 9.90. This is because the scale is fixed at 2 digits after the decimal point.

Similarly, if you attempt to store a value greater than 99.99 in a decimal(4,2) column, an error will be thrown. This is because the precision of 4 restricts the total number of digits that can be stored.

Let’s take a look at an example:

DECLARE @Var DECIMAL(4,2)
SET @Var = 99.99
SELECT @Var

When you run the above script, it will return 99.99 as expected. However, if you try to run the following script:

DECLARE @Var DECIMAL(4,2)
SET @Var = 999.9
SELECT @Var

You will encounter an error:

Msg 8115, Level 16, State 8, Line 7
Arithmetic overflow error converting numeric to data type numeric.

This error occurs because the value 999.9 exceeds the maximum allowed precision of 4.

Understanding the Decimal datatype and how precision and scale work is crucial for accurate data storage and retrieval in SQL Server. If you have any further questions or need clarification, feel free to reach out to me on Twitter.