SQL Server precision and scale problems (SQL Server precision problem)

Ref: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-precision-and-scale-problems

 

SQL Server precision and scale problemsby George mastros

24 Nov 2008,Categories:Data Modelling & design, Microsoft SQL Server

Specified 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:

Tsqlline number on/off | show/hide | select all

2. Select 10/3UnionAll
4. Select 10/3.0UnionAll
6. Select 10/3.00UnionAll
8. Select 10/3.000UnionAll
10. Select 10/3.0000UnionAll
12. Select 10/3.00000UnionAll
14. Select 10/3.000000UnionAll
16. Select 10/3.0000000UnionAll
18. 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.

Tsqlline number on/off | show/hide | select all

2. Select SQL _variant_property(3,'Baseetype') As [Base type],
4. SQL _variant_property(3,'Precision') As [Precision],
6. SQL _variant_property(3,'Scale') As [Scale],
8.  
10. SQL _variant_property(10,'Baseetype') As [Base type],
12. SQL _variant_property(10,'Precision') As [Precision],
14. 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

Tsqlline number on/off | show/hide | select all

2. Select SQL _variant_property(3.0,'Baseetype') As [Base type],
4. SQL _variant_property(3.0,'Precision') As [Precision],
6. SQL _variant_property(3.0,'Scale') As [Scale],
8.  
10. SQL _variant_property(10,'Baseetype') As [Base type],
12. SQL _variant_property(10,'Precision') As [Precision],
14. 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 ). please Ming 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 already Med!

Select 10/3 3.0000000000 int
Select 10/3 .0 3.3333330000 decimal)
Select 10/3 .00 3.3333330000 decimal)
Select 10/3 .000 3.3333330000 decimal)
Select 10/3 .0000 3.3333330000 decimal)
Select 10/3 .00000 3.3333333000 decimal)
Select 10/3 .000000 3.3333333300 decimal)
Select 10/3 .0000000 3.3333333330 decimal)
Select 10/3 .00000000 3.3333333333 decimal)