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

Source: Internet
Author: User

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

Tsqlline number on/off | show/hide | select all
  1. Select SQL _variant_property(3,'Baseetype') 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,'Baseetype') 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

Tsqlline number on/off | show/hide | select all
  1. Select SQL _variant_property(3.0,'Baseetype') 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,'Baseetype') 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 ). 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)

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.