There are so many confusions arise when we talk about decimal data type among

novice developers. In SQL Server forums also I have seen many persons asking questions related to decimal types. The confusion is actually because of interpreting the values of precision and scale defined for the decimal data type. For example DECIMAL (5, 4) can be easily interpreted by them as 00000.0000, that is, five zeros before the decimal point and four zeros after the decimal point.

Actually, this is not the case. This three part series explain the decimal data type, the precision and scale and how this is used in T-SQL.

**Basics**

A good understanding of decimal arithmetic will help you to make good decisions when designing columns with decimal data types. This post is mostly based on IEEE 754 standard for floating point arithmetic.

Any number (a finite number) can be represented by three parameters as follows,

*Sign*– in which 0 indicates a positive number and 1 indicates a negative number*Coefficient*– is an integer number that can be zero or positive, and*Exponent*– is a signed integer that represents a power of ten that has to be multiplied by the coefficient

**An abstract representation of a decimal number**

Let me arbitrarily take an integer number 123450. Dividing the number by 10 yields the decimal number 12345.0. Similarly, dividing the number by 100 yields the decimal number 1234.50. We can formulate this as,

123450 / 10 = 123450 / 10^{1} = 12345.0

123450 / 100 = 123450 / 10^{2} = 1234.50

123450 / 1000 = 123450 / 10^{3} = 123.450

We can also write this as;

123450 X 10^{-1} = 12345.0

123450 X 10^{-2} = 1234.50

123450 X 10^{-3} = 123.450, etc.

The integer number 123450 is the *coefficient* and the numbers -1, -2, -3 etc are called the *exponents*. So to represent the decimal number123.450, we need the coefficient 123450 and the exponent -3. And also a *sign* bit 0 for representing a positive decimal. So a numerical value of a finite number can be represented by:

(-1)* ^{sign}* X

*coefficient*X 10

^{exponent}And also there are 6 *significant digits* in the *coefficient*. Many abstract arithmetic ** operations** are defined on the decimal number such as

*abs, add, subtract, multiply, divide, min, max*to name a few. One problem arises during the arithmetic operation on decimal number. The abstract model does not specify a limit to the value of coefficient. However a computer system represents data in a precise format (bytes). So during the arithmetic operations like multiply and divide, the number of significant digits in a resulting coefficient may increase. Now we are forced to

**the resultant decimal to fit in to the desired significant digits.**

*round*Various algorithms are used by the computer system, if the result from a decimal arithmetic operation needs to be rounded. IEEE 754 defines five algorithms for rounding, that are, *round-down, round-half-up, round-half-even, round-ceiling, round-floor*. I’m not going in detail to the rounding algorithms here.

**SQL Server representation**

In the previous section, we have seen how a decimal number can be represented by an abstract model provided by IEEE 754 standard. The ISO/ANSI SQL standard also defines exact numeric data types, based on that model and this is what SQL Server has been implemented.

The domain (the range of values) of decimal data type in SQL is defined by two terms: the precision and the scale. *Precision (P)** is a positive integer that determines the number of significant digits.* This is same as the significant digit of the coefficient in the abstract decimal model. For example a precision of 1 means only one digit can be stored as the coefficient, that is, any single digit from -9 to +9. The following table summarizes the precision:

Precision Coefficient Coefficient

(Range From) (Range To)

1 -9 +9

2 -99 +99

3 -999 +999

4 -9999 +9999

5 -99999 +99999

6 -999999 +999999

The ** scale (S)** is a non-negative integer in SQL. To get the exact decimal value, the coefficient value must be multiplied by 10

*. Did you notice anything similar to the abstract model of decimal with this? Yes, the scale is the exponent. Note that a scale of 0 represents an integer value in SQL.*

^{-S}The ** scale (S)** is a non-negative integer in SQL. To get the exact decimal value, the coefficient value must be multiplied by 10

*. Did you notice anything similar to the abstract model of decimal with this? Yes, the scale is the exponent. Note that a scale of 0 represents an integer value in SQL.*

^{-S}For example, DECIMAL (5, 1) represents precision 5, and scale 1 and that indicate the number has five digits in total in which one digit is after the decimal point. Scales (S) can be range from 0 to *precision*. The below table summarizes various scales for a precision of 5 as an example:

Precision : 5, Coefficient: 99999

Scale 10^{-S} CoefficientX10^{-S} CoefficientX10^{-S}

(Range From) (Range To)

0 10^{-0} -999999 +999999

1 10^{-1} -99999.9 +99999.9

2 10^{-2} -9999.99 +9999.99

3 10^{-3} -999.999 +999.999

4 10^{-4} -99.9999 +99.9999

5 10^{-5} -9.99999 +9.99999

So the DECIMAL (5, 4) in the conversion at the start of this post turns out to be in the range of -9.9999 and +9.9999. So we cannot assign a value of 100.0000 to this.

In the next part we will see how SQL Server internally represents decimal numbers.