How to decide the precision and scale of a decimal column – Part 1

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

Imagenovice 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,

  1. Sign – in which 0 indicates a positive number and 1 indicates a negative number
  2. Coefficient – is an integer number that can be zero or positive, and
  3. 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 / 101 = 12345.0

123450 / 100   = 123450 / 102 = 1234.50

123450 / 1000             = 123450 / 103 = 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 10exponent

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 round the resultant decimal to fit in to the desired significant digits.

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-S. 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.

The scale (S) is a non-negative integer in SQL. To get the exact decimal value, the coefficient value must be multiplied by 10-S. 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.

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.

Advertisements

Leave a comment

Filed under Basics

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s