Differences between decimal (numeric), float, and real data types in SqlServer

A decimal data type can store up to 38 numbers. All numbers can be placed on the right of the decimal point. The decimal data type stores an accurate (accurate) numeric expression, and does not store approximate values. Two features of defining decimal columns, variables, and parameters are as follows: the sum of the numbers on the left and right of p decimal points, excluding decimal points. For example, 123.45

A decimal data type can store up to 38 numbers. All numbers can be placed on the right of the decimal point. The decimal data type stores an accurate (accurate) numeric expression, and does not store approximate values. Two features of defining decimal columns, variables, and parameters are as follows: the sum of the numbers on the left and right of p decimal points, excluding decimal points. For example, 123.45

DecimalThe data type can store up to 38 numbers, and all numbers can be placed on the right of the decimal point.DecimalThe data type stores an accurate (accurate) numeric expression, and does not store approximate values.

DefinitionDecimalThe columns, variables, and parameters are as follows:

· P indicates the sum of digits on the left and right of the decimal point, excluding the decimal point. For example, 123.45, p = 5, s = 2.

Specifies the number of numbers that can be controlled by precision or object.

· S

Specify the number of decimal places or numbers to the right of the decimal point.

P and s must comply with the following rules: 0 <= s <= p <= 38.

NumericAndDecimalThe default maximum precision of the data type is 38. In Transact-SQL,NumericAndDecimalThe data type is functionally equivalent.

When the data value must be precisely stored as specifiedDecimalData Type to store numbers.

FloatAndRealData

FloatAndRealData types are called similar data types. In terms of approximate numeric data types,FloatAndRealThe use of data follows the IEEE 754 standard.

The approximate numeric data type does not store the exact values specified for most numbers. They only store the approximate values of these values. In many applications, the tiny difference between the specified value and the stored value is not obvious. However, sometimes these differences are worth noting. BecauseFloatAndRealThis approximation of the Data Type, when the exact numeric State is required,For example, in a financial application, these data types are not used in operations that require rounding, or in operations that require equivalent verification. UseInteger,Decimal,MoneyOrSmallmoneData type.

In the WHERE clause Search Condition (especially the = and <> operators), avoid usingFloatOrRealColumn. Best PracticeFloatAndRealColumn> or <comparison.

The IEEE 754 specification provides four rounding modes: Rounding to the nearest value, rounding up, downloading, and rounding to zero. Microsoft®SQL Server™Use top rounding. All values must be accurate to the specified precision, but small floating point values may change. Because the binary representation of floating point numbers can use any of the many valid rounding rules, it is impossible for us to reliably quantify a floating point value.

ConversionDecimalAndNumericData

ForDecimalAndNumericData Type, Microsoft®SQL Server™Each specific combination of precision and decimal places is considered as a different data type. For example,Decimal(5, 5) AndDecimal(5, 0) Is treated as different data types. (Therefore, variables used in Stored Procedures use Real or Float instead of decimal)

In a Transact-SQL statement, constants with decimal points are automatically convertedNumericThe minimum precision and decimal places must be used. For example, constant 12.345 is convertedNumericValue. The precision is 5 and the decimal point is 3.

SlaveDecimalOrNumericDirectionFloatOrRealConversion will cause loss of precision. SlaveInt,Smallint,Tinyint,Float,Real,MoneyOrSmallmoneyDirectionDecimalOrNumericConversion may cause overflow.

By default, When you convert a number to a lower-precision or decimalDecimalOrNumericSQL Server uses the rounding method. However, if the set arithabort option is ON, SQL Server will encounter an error when overflow occurs. If only precision and decimal places are lost, no error is generated.

The root cause of the Float scientific notation and value problem is that the float type itself is an inaccurate data representation method, there may be a little bit of error when taking it out, and this little bit of error will cause data inconsistency during data comparison.