Shulou(Shulou.com)06/01 Report--

SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency. This article describes the dangers of using Float or Real data types.

Floating-point data types can hold very large numbers, but lack precision. They are convenient for some types of scientific calculations, but dangerous when used more widely because they introduce larger rounding errors.

Floating-point operations tolerate and manage approximations in order to avoid overflow errors in calculations. In the real world, we are usually concerned about the accuracy of numbers and sacrifice space and resources to avoid spillovers.

Science works within the margin of error, and accuracy is very important in business accounting. When I was a junior programmer, I wrote about what I thought was a very appropriate way for banks to calculate trading profits. I am satisfied that there is a margin of error of one or two pence at most out of 1 million pounds. It uses the calculations inherent in the PL / 1 compiler that we used to develop the financial package at that time. I showed them the elaborate application, and they were shocked. The ruthless bankers said mercilessly that 1 million pounds was missing a few cents. They won't accept it. I was forced to write a precise binary coded decimal (BCD) package in precise assembly code.

SQL Prompt has code analysis rules (BP023) that remind you to use Float or Real data types because they may introduce the kind of calculations that many organizations typically perform on their SQL Server data.

Data type of approximate number

Floating-point operations are designed at a time when priority is given to saving memory while providing a general method involving a large number of operations. Although it is still useful for many types of scientific calculations, especially those that conform to the floating-point arithmetic double-precision IEEE 754 standard, it must be a compromise. The clue is the name of this data and arithmetic: "approximation". Floating-point numbers cannot accurately represent all real numbers: in addition, floating-point operations cannot accurately represent all arithmetic operations. However, even if numbers are not always kept precisely, the range of numbers they can retain is much larger than that of other numeric types.

Problems caused by the use of floating-point operations are caused by rounding in complex calculations, and if the data is in a "bad condition" state, the most common problem is that subtle changes in the input are magnified in the output. With the improvement of the accuracy of digital representation, this inaccuracy has become less obvious, but they still exist. There are also some esoteric limitations when using numbers that are valid but cannot be represented by floating-point numbers, such as tan (π / 2), but these may only interest mathematicians.

SQL Server floating point data type

The SQL standard has three floating points, approximate data types, REAL, DOUBLE PRECISION, and FLOAT (n). SQL Server meets this requirement except that it does not have a DOUBLE PRECISION data type and uses FLOAT (53) instead. The FLOAT (24) and FLOAT (53) data types correspond to Binary32 (Single) and Binary64 (double) in the IEEE 754 standard and are stored in 4 and 8 bytes, respectively, and retain 7 and 16 digits, respectively. They are useful when calculations produce the same results as applications that use the .NET Framework that also uses IEEE 754. When the size of the number exceeds the maximum allowed for the DECIMAL data type (38 bits), a double precision type is also required, but the precision is reduced. Of course, approximations cannot be reliably used in any equality test, such as the WHERE clause.

Calculation using the REAL data type (single precision)

I will try the REAL data type. FLOAT (24) data types or smaller data types react in the same way. When experimenting with floating-point numbers in SQL Server, the first thing to remember is that SSMS presents floating-point numbers in a way that masks small differences. For example:

SELECT Convert (REAL,0.100000001490116119384765625)

... Get 0.1

To see exactly what values are stored in floating-point numbers, you must use the STR () function to specify the precision you actually need.

This is already worrying. After all, we are dealing with millions of rows of data, so unless results such as banker rounding are averaged, small errors pile up. This mistake is close to the "million pound penny" I mentioned in the introduction.

Let's avoid using 0.1 and boil it down to a strange floating point number. How about 1 divided by 3? Surely that's not a problem, is it?

Alas, it is wrong. OK, this is a small mistake, but please remember my story about bankers. Yes or no, there is no gray shadow for a man in a gray suit. In business school, there is only one mark and one fork. There is no sign that says "close enough".

A simple test is to divide the number 1 by 1 to 20. What will go wrong?

We can store the results of floating-point numbers and numeric calculations, convert them to strings, and then compare strings (note that the string STR () can be placed in leading spaces, which complicates the situation).

Now, what if we list lines where the numbers don't match?

Aah! The result is correct only if the divisor is 1, 2, 4, 8, or 16.

If you want the floating-point number to be accurate to some extent, but the numerical version is incorrect, here is the numerical quotient calculated in Excel:

Calculations using FLOAT (25) or higher (double precision)

If you use a double-precision floating point FLOAT (25) or higher, all tests will pass because the STR () function allows up to 16 digits to the right of the decimal point. If it is greater than 16, the result is truncated. The double-precision data type has 16 digits, while the single-precision data type has 7 digits. You will also see that the single-precision data type correctly fetches the first seven numbers. Similarly, double precision will correctly get the first 16 digits. We can enlarge the number to see the approximate value.

DECLARE @ FirstApproximate FLOAT (53) = 10000000000000000.1SELECT Str (@ FirstApproximate,40,16) AS BigNumberWithaDecimal

That little part is gone, isn't it? This may be a small difference, but in some calculations, it can cause problems.

Conclusion

The floating-point algorithm is fast and economical in storage, but provides approximate results. It is suitable for scientific applications with good conditions, but not for financial calculations, because financial calculations require numbers to be "right" or "wrong". It has additional drawbacks in the database because you cannot reliably and consistently test whether two approximations are equal.

It is incorrect to say that you should never use floating-point numbers in SQL data types or arithmetic. In the SQL standard, there is a specific type of approximation. Today, in SQL Server with appropriate requirements, I always insist on using double-precision floating-point data types. They are well suited for purposes such as modeling weather systems or drawing trajectories, but they are not suitable for the types of calculations that ordinary organizations may use databases.

If you find that these types are used incorrectly, you should use the appropriate DECIMAL/ NUMERIC type instead. If you know you need a floating-point algorithm and can explain why, then you probably know enough to avoid floating-point traps.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.