Accidentally and in the middle of the ambush of the decimal--a new look at the relevant knowledge of decimal

We all know that in the program language and the database of various numeric types decimal,numeric,float,double,real ..., sometimes inadvertently will be a problem, in addition to mastering the basic principles of data storage, but also to carefully understand the various languages and products in the details of the difference, even so, It was possible to ambush by carelessness. In SQL Server, decimal can be used to store integers, or to store decimals, and with high precision, so I often use them to save data-related fields like sales classes. scene reappearanceA data table structure is as follows:

CREATE TABLE demo
(
ID bigint,
amount Decimal (18,5),--Total amount
Quantity Decimal (18,5)--Quantity
)

In writing a feature which uses a sentence

Select CONVERT (Decimal (18,2), amount)/convert (decimal (18,2), quantity) from demo where quantity>0.00

Problem solving and analysisIn the process of execution always reported "by 0 to divide the error", look at the code to think that there is no problem, carefully read before you understand that the original thought I want to the results only need to retain 2 decimal places, so do the conversion after the calculation, and on the one hand is quantity>0.00 judgment and the previous conversion mismatch caused. Although 0.00001 is greater than 0.01 to match the filter
But the transformation becomes 0.00, so the above error will occur. The range of digits in SQL 2005 that can be stored in decimal is-10^38 +1 to 10^38-1 (see reference 1), with scientific notation to see its power (^-^), and the maximum positive number is 4 100 million multiplied by 10,000 minus 1. This is a very large number. Back to the point, then we defined two fields are decimal (18,5), the precision is 18, decimal is 5 bits, but note that MS SQL Median is 10 digits, that is, decimal (18,5) Represents a number between 9999999999999.99999 and 9999999999999.99999. There is a big difference between decimal (18,5) and Decimal (18,2), which can be considered to be different data types (see reference 2), which is likely to be lost if you convert between the two data types. The solution is simple, change directly to the calculation after the conversion on the line.

Select CONVERT (Decimal (18,2)/(amount/quantity) from demo where quantity>0.00000

RethinkingIn fact, solving the above problems is very simple. The reason for "fuss", the main still think that the data type Master is not fine enough, always feel that the data type, probably right on the line, almost on the line, in fact this is wrong. stretch your thoughts and see the decimal in C #In development, C # is often used to read the values stored in the database, and it is necessary to define the corresponding variables to access. In C #, Decimal is a 128-bit exact data type, roughly the range of ±1.0x10^-28 to ±7.9x10^28, smaller than the maximum range of SQL decimal, but 99.999999 of the current human ... 999,999,999% of applications should be fully satisfied (see reference 3). How decimal is stored in the computer.   (see reference 4). Reference 1:decimal and numeric (Transact-SQL) reference 2:sql server–difference and explanation among decimal, FLOAT and numeric
Reference 3:decimal (C # Reference) reference 4:http://topic.csdn.net/u/20090505/14/2859ab0f-579a-467b-b03a-c792bfb98bc6.html