Differences between decimal (numeric), float, and real data types in SQL Server [goto]

Decimal (numeric) synonymous for precise storage of numeric values

float and real cannot store values accurately

The decimal data type can store up to 38 digits, and all numbers can be placed to the right of the decimal point. The decimal data type stores an accurate (precise) representation of the number, and does not store the approximate value of the value.

The two characteristics of a column, variable, and parameter that define a decimal are as follows:

• P The sum of the digits to the left and right of the decimal point, excluding the decimal point. such as 123.45, then p=5,s=2.

  Specifies the precision or number of digits that the object can control.

• S

  Specifies the number of decimal places or numbers that can be placed to the right of the decimal point.

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

The default maximum precision value for the numeric and decimal data types is 38. In Transact-SQL,numeric is functionally equivalent to the decimal data type.

When data values must be stored exactly as specified, a decimal data type with decimals can be used to store numbers.

float and real data

The float and real data types are referred to as approximate data types. With respect to approximate numeric data types, the use offloat and real data follows the IEEE 754 standard.

Approximate numeric data types do not store the exact values specified for most numbers, they only store the most approximate values for those values. In many applications, the small difference between the specified value and the stored value is not obvious. But sometimes these differences deserve attention. Because of this approximation of the float and real data types, these data types are not used when precise numeric states are required, such as in financial applications, in operations that require rounding, or in an equivalence check. This is when you want to use integer,decimal, Money, or smallmone data types.

In the WHERE clause search condition (especially the = and <> operators), avoid using the float or the real column. It is best to limit the use of float and real columns for > or < comparisons.

The IEEE 754 specification provides four rounding modes: rounding to the nearest value, rounding up, rounding down, and rounding to zero. Microsoft®sql Server™ used on rounding. All values must be accurate to a definite precision, but will produce a small floating-point value change. Because the binary representation of floating-point numbers can take any one of many legitimate rounding rules, it is not possible to reliably quantify a floating-point value.

Convert decimal and numeric data

For decimal and numeric data types, Microsoft®sql Server™ considers 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. ( so the variables used in the stored procedure are either real or Float, rather than the decimal type )

In Transact-SQL statements, constants with decimal points are automatically converted to numeric data values, and the minimum precision and scale must be used. For example, a constant of 12.345 is converted to a numeric value with a precision of 5 and a decimal bit of 3.

Converting from decimal or numeric to float or real can result in a loss of precision. from int,smallint,tinyint,float,real, money, or smallmoney converting to a decimal or numeric causes an overflow.

By default, when you convert a number to a decimal or numeric value that has a lower precision and scale, SQL Server uses the round-off method. However, if the SET ARITHABORT option is on, SQL Server will receive an error when an overflow occurs. If only the precision and scale are lost, no error is generated.

Differences between decimal (numeric), float, and real data types in SQL Server [goto]