# LessThanDot

Data Management

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

## LTD Social Sitings

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

## XML Feeds

 « SQL Server efficient handling of divide by zero Setting a standard DateFormat for SQL Server »

# SQL Server Precision And Scale Problems

Many people are confused about SQL Server's precision and scale. This is unfortunate because choosing the correct values for precision and scale is critically important when you perform math operations using the decimal/numeric data type. The point of this blog is to explain how SQL Server determines the data type for math operations, and the order in which conversions occur.

For example:

tsql
1. Select 10 / 3           UNION All
2. Select 10 / 3.0         UNION All
3. Select 10 / 3.00        UNION All
4. Select 10 / 3.000       UNION All
5. Select 10 / 3.0000      UNION All
6. Select 10 / 3.00000     UNION All
7. Select 10 / 3.000000    UNION All
8. Select 10 / 3.0000000   UNION All
9. Select 10 / 3.00000000

Let's take a close look at the above query so that we can predict the output. Of course, it will help if we know the data types that SQL Server uses. There is a relatively obscure function that you can use to determine the data types. SQL_VARIANT_PROPERTY

For the first calculation, we have 10 / 3. We all know the answer is 3 1/3, but how is this expressed in the output?

Well, using the SQL_VARIANT_PROPERTY function, we can determine the data type that SQL Server will use.

tsql
1. Select SQL_VARIANT_PROPERTY(3, 'BaseType') As [Base Type],
2.        SQL_VARIANT_PROPERTY(3, 'Precision') As [Precision],
3.        SQL_VARIANT_PROPERTY(3, 'Scale') As [Scale],
4.
5.        SQL_VARIANT_PROPERTY(10, 'BaseType') As [Base Type],
6.        SQL_VARIANT_PROPERTY(10, 'Precision') As [Precision],
7.        SQL_VARIANT_PROPERTY(10, 'Scale') As [Scale]

The output indicates SQL Server will use Integer, Precision 10, scale 0. Using integer math, the output will be 3. Since both values are integer, the result is an integer.

Now, let's look at the next one. 10/3.0

tsql
1. Select SQL_VARIANT_PROPERTY(3.0, 'BaseType') As [Base Type],
2.        SQL_VARIANT_PROPERTY(3.0, 'Precision') As [Precision],
3.        SQL_VARIANT_PROPERTY(3.0, 'Scale') As [Scale],
4.
5.        SQL_VARIANT_PROPERTY(10, 'BaseType') As [Base Type],
6.        SQL_VARIANT_PROPERTY(10, 'Precision') As [Precision],
7.        SQL_VARIANT_PROPERTY(10, 'Scale') As [Scale]

This time, we get Numeric(2,1) (for 3.0) and int for the 10. There are well defined (although obscure) rules for math operations. Full rules here: Precision, Scale, and Length

For division, the rule is:

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Scale = max(6, s1 + p2 + 1)

p1 represents the precision of the first number. s1 represents the scale of the first number. P2 and S2 represent the second number.

10 / 3.0
P1 = 10
S1 = 0
P2 = 2
S2 = 1

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Precision = 10 - 0 + 1 + max(6, 0 + 2 + 1)
Precision = 11 + Max(6, 3)
Precision = 11 + 6
Precision = 17

Unfortunately, this isn't correct because the SQL_VARIANT_PROPERTY is returning 10 for the integer. When dividing the numbers, SQL Server actually converts the integer to a decimal, using the smallest value possible to represent the value. In this case, 10 is converted to decimal(2,0). Performing the calculations again:

10 / 3.0
P1 = 2
S1 = 0
P2 = 2
S2 = 1

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Precision = 2 - 0 + 1 + max(6, 0 + 2 + 1)
Precision = 3 + Max(6, 3)
Precision = 3 + 6
Precision = 9

Scale = max(6, s1 + p2 + 1)
Scale = max(6, 0 + 2 + 1)
Scale = max(6,3)
Scale = 6

So, Select 10 / 3.0 = 3.333333

Now, let's fast forward to the last calculation. Select 10 / 3.00000000

Precision/scale for the 10 (after converting to decimal) = 2,0
Precision/scale for the 3.00000000 = 9,8

10 / 3.00000000
P1 = 2
S1 = 0
P2 = 9
S2 = 8

Precision = p1 - s1 + s2 + max(6, s1 + p2 + 1)
Precision = 2 - 0 + 8 + max(6, 0 + 9 + 1)
Precision = 10 + Max(6, 10)
Precision = 10 + 10
Precision = 20

Scale = max(6, s1 + p2 + 1)
Scale = max(6, 0 + 9 + 1)
Scale = max(6,10)
Scale = 10

The result is 3.3333333333 Decimal(20,10)

Lastly, since the original example was a UNION ALL query, all results are converted to the same data type. Each result is first calculated, then finally converted to a data type that satisfies all results. In this case, each result is converted to a Decimal(20,10). But remember, this ONLY occurs after each calculation is performed!

```Select 10 / 3           3.0000000000 Int
Select 10 / 3.0         3.3333330000 Decimal(9,6)
Select 10 / 3.00        3.3333330000 Decimal(10,6)
Select 10 / 3.000       3.3333330000 Decimal(11,6)
Select 10 / 3.0000      3.3333330000 Decimal(12,6)
Select 10 / 3.00000     3.3333333000 Decimal(14,7)
Select 10 / 3.000000    3.3333333300 Decimal(16,8)
Select 10 / 3.0000000   3.3333333330 Decimal(18,9)
Select 10 / 3.00000000  3.3333333333 Decimal(20,10)
```
9350 views

Comment from: SQLDenis [Member]
Very nice post George
I am still amazed at the number of people who get bitten by integer math these days
11/24/08 @ 09:04
Comment from: kaht [Member]
Interesting.
11/24/08 @ 09:22
Comment from: Joe Celko [Visitor] · http://www.celko.com
To make thing s worse, scale and precision are implementation defined in the SQL Standards. This makes porting code and data harder than we might like it to be. Only COBOL seems to have spent poem time on rules for this stuff ...
11/29/08 @ 14:07
Comment from: Albert Lumu [Visitor] · http://myc4.com
Man, I came across this after fighting a precision bug for the last 24hrs+

Thanks!
01/13/09 @ 07:14
Comment from: George Mastros (gmmastros) [Member]
Albert Lumu,

I can't imagine fighting a bug for 24 hours. The next time this happens, I would encourage you to seek help in the Less Than Dot forum, here:

http://forum.lessthandot.com/viewforum.php?f=17

01/13/09 @ 07:21