Physical database design-understanding floating point numbers

I. Introduction to floating-point types

An integer is a useful data type, but it can only store integers, such as 1, 199, 19. But it cannot express a floating-point number like 2.5. If the data is highly accurate, you need to use a different data type to replace the integer type. For example, when calculating money, it usually needs to be accurate to two digits after the decimal point, like $19.95.

Here is the first introduction to the floating-point numbers in SQL Server:

Floating-point types	is accurate	Description
Decimal	Precise numerical type	-Numerical data of fixed accuracy and range of 1038-square +1~1038-1
Numeric	Precise numerical type	Same decimal
Float	Approximate numerical type	Floating-point number between -1.79e+308~1.79e+308
Real	Approximate numerical type	Floating-point number between -3.40e+38~3.40e+38
Money	Currency type	-263~263-1 Currency data, accurate to one out of 10,000
SmallMoney	Currency type	-214748.3648~214748.3647 Currency data, accurate to one out of 10,000

2. In SQL Server, both decimal and numeric are exactly the same.
4. The first parameter is the precision (number of total digits), and the second parameter is the scale (the number of digits after the decimal point). such as numeric (5,2) indicates a total length of 5 bits, two digits after the decimal point.
6. The total length and the number of digits after the decimal point must conform to the precision and scale, no more and no less.
8. Decimal and numeric represent the exact value that is stored when it is saved, while float and real store approximate values, and the actual stored value may be slightly larger or smaller than the value to be stored.
10. In the WHERE clause, you should avoid using the float or the real column.
12. SQL Server views each specific combination of precision and scale as a different data type. For example,decimal(5,5) and decimal(5,0) are treated as different data types.
14. Converting from decimal or numeric to float or real can result in a loss of precision. converting from int, smallint, tinyint, float, real, money, or smallmoney to decimal or numeric can result in overflow. Near-loss accuracy SQL Server does not error, overflow, SQL Server will error.

16. In SQL Server, the money and smallmoney types are used to represent the currency type, which is 8 bytes and smallmoney is 4 bytes. The money type is similar to decimal (19,4), but the difference is that its precision and scale can be entered at any length, rather than the decimal type.

18. Money and SmallMoney are limited to 4 digits after the decimal point. If you need more bits after the decimal point, use the decimal data type.

20. Comma delimiter is not allowed in money or smallmoney constants. You can only specify a comma delimiter in a string that is explicitly converted to money or smallmoney.
22. Money,smallmoney,decimal,numeric in SQL Server corresponds to the decimal in C #, float,real corresponds to float in C #.

second, anti-pattern: using float type

Most programming languages support real types, using the keyword float or double. SQL also supports similar data types using the same keywords. It is natural for many programmers to use the SQL float type where they need to use floating-point numbers because they are accustomed to programming with the float type.

　　2.1 Necessity of rounding

Not all of the information described in decimal can use binary storage. In some necessary factors, floating-point numbers are usually rounded to a very close value.

For example, 1/3 with an infinite loop of decimal can be represented as 0.333 ..., the real value cannot be completely written out, because there is an infinite number of 3 to write. The number of digits after the decimal point indicates the accuracy of the number, so an infinite loop is written down to 3, which can be infinitely close to the exact value of 1/3.

In IEEE 754, a floating-point number is used in binary notation. The expression of infinite decimals in decimal in binary is completely different. However, some decimal finite fractions, such as 59.95, need to be represented as infinite decimals in binary. The float type cannot express infinite decimals. Thus, it stores the value closest to 59.95 in the binary representation, which may be equal to 59.950000762939 in decimal notation.

SELECT * 1000000000  from  Number WHERE = 1

return value: 59950000762.939

For some operations, such errors are not tolerated.

Another example is the comparison operation with float

SELECT *  from  Number WHERE = 59.95

The above query statement may return NULL.

Sometimes, the correct result may be returned for the following SQL statement:

SELECT *  from  Number ABS - 59.95 < 0.000001

But one more 0, the higher the accuracy requirement, the better the result is not being able to return:

SELECT *  from  Number ABS - 59.95 < 0.0000001

The cumulative effect of non-precision floating-point numbers will be greater for total operations other than sums. Although the error appears to be very small, its cumulative effect cannot be ignored. For example, 1*0.999 1000 times the result is 0.3677. The greater the number of executions, the greater the error.

　　2.2 Identifying anti-patterns

A design that uses any float, real, or double type can be anti-pattern, and the design is not inappropriate when the range of floating-point numbers used by the application does not need to reach the maximum/minimum range defined by the IEEE754 standard. For example, you only need to be accurate to 3, 4 digits after the decimal point, and there is nothing wrong with the float type.

Second, float is your choice when you need to store data that has a large range of values that are larger than the range supported by the two types int and numeric. The program of Scientific Computing class is the general application of float.

Iii. Solutions: Using the numeric type

Use the numeric or decimal type in SQL to replace float and its similar data types for fractional storage of fixed precision.

ALTER TABLE  Number ADD Num NUMERIC (9,2);

These data types store data precisely based on the precision that you specify when you define this column. Define the type of the column with precision as the type parameter, similar to the way the varchar is followed by length. Its precision refers to the number of valid digits that each value in this column can contain at most. 9 means you can store 123456789. The second parameter specifies its scale. The scale here refers to the number of digits after the decimal point. The number of the decimal part is also counted in its effective bit, so the accuracy of 9 ticks 2 means that 1234567.89 can be stored. After the decimal point can not be more or less, the total number of digits can not be less.

At this point, if you scale the value 1 billion times times more, you'll be sure to get the expected values:

SELECT *  from  Number WHERE = 59.95

59950000000. The two types of numeric and decimal behave the same, without any difference.

If you need to represent the decimal number exactly, use the numeric type. The float type cannot represent many decimal rational numbers, so they should be treated as non-precise values.

Physical database design-understanding floating point numbers