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

Decimal (numeric) is synonymous and used to precisely store values.

Float and real cannot accurately store values.

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 the numbers on the left and right of the decimal point, excluding the decimal point. For example, 123.45, P = 5, S = 2.

  Specify the precision orObjectNumber of numbers that can be controlled.

• S

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

  P and S must follow 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.

Float and real data

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 applicationsProgramThe small 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 requires precise numerical states, such as in financial applications, in operations that require rounding, or in operations that require equivalent verification, these data types are not used. UseInteger,Decimal,MoneyOrSmallmoneData type.

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

The IEEE 754 specification provides four rounding modes: Rounding to the nearest value, rounding up, downloading, and rounding to zero. Microsoft SQL Server uses 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.

Convert decimal and numeric data

ForDecimalAndNumericData Type. Microsoft SQL Server regards each specific combination of precision and decimal places as different data types. For example,Decimal(5, 5) AndDecimal(5, 0) Is treated as different data types.

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 value of decimal or numeric with lower precision and decimal places, SQL 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.