The difference of real, float, decimal and numeric data types in SQL

Time:2020-3-30

Summary:

Floating point data types include real type, float type, decimal type and numeric type. Floating point data types are used to store decimal numbers.

In SQL server, the data of floating-point value is stored by round up. The so-called round up means that the decimal part to be rounded is regardless of its size,

As long as it is a non-zero number, add 1 to the least significant digit of the number and carry out the necessary carry.

Because floating-point data is approximate, not all data in the range of data types can be represented accurately.

 

1. Difference description

Decimal (numeric): synonymous, used to store values accurately

Float and real: values cannot be stored accurately

 

2. Real data type

The storage size of real data is 4 bytes, which can be accurate to the 7th digit after the decimal point.

The data storage range of this data type is from – 3.40e + 38 to – 1.18e-38, 0 and 1.18e-38 to 3.40e + 38.

In MSSQL, the synonym of real is float, with almost the same usage.

 

3. Float data type

The data storage size of float type is 8 bytes, which can be accurate to the 15th digit after the decimal point.

The data storage range of this data type is from – 1.79e + 308 to – 2.23e-308, 0 and 2.23e + 308 to 1.79e + 308.

Float (size, d) a small number with a floating decimal point. Specify the maximum number of digits in parentheses. Specify the maximum number of digits to the right of the decimal point in the D parameter.

Data of float type can be written in the form of float [(n)]. Where n is an integer value between 1 and 15, specifying the precision of float data. When n is 1-7, in fact, the user defines a real type of data, and the system uses 4 bytes for storage;

When n is 8-15, the system thinks it is a float type data and stores it with 8 bytes. This not only enhances the flexibility of data definition, but also saves space.

 

4. Decimal (numeric) data type

The function of decimal data type is the same as that of numeric data type. Both of them can provide the actual storage space needed for decimal,

However, there are some limitations. Users can store data with 2-17 bytes, and the value range is – 1038 + 1-1038-1.

Decimal (size, d) is a double type stored as a string, allowing fixed decimal points. Specify the maximum number of digits in parentheses.

Where size represents the total number of digits (excluding decimal point) of the value that can be stored, the default value is 18; D represents the number of digits after the decimal point, and the default value is 0;

The relationship between parameters is 0 ≤ D ≤ size. For example: decimal (15,5) means there are 15 digits in total, including 10 digits of integer and 5 digits of decimal.

If a number is a rounding up number, its absolute value does not decrease. For example, rounding 3.14159265358979 to 2 and 12 bits respectively, the results are 3.15 and 3.141592653590.

There is still a big difference in precision, so when creating a database, it is necessary to analyze the data type of the field accurately to ensure that there is no error in the maximum precision of the inserted data value so as not to cause big deviation in the process of data operation.

For example, the data queried at the end of a month or at the end of a year’s summary report is a decimal, and the data after calculation is also a decimal, but when it is added to the summary report, it becomes an integer,

This situation is often the problem that tables in the database are not effectively set the precision of floating-point data after the decimal point when they are established, which makes their data round up.

Explain:

Rounding is a rule of rounding. In our daily life, in order to simplify the format and memory, we often use the rounding method to get rid of the odd or round an integer to solve this problem.

Rounding is a very flexible method. Therefore, no matter in daily life or in mathematical calculation, rounding method is widely used.

In addition to rounding method, there are other rounding rules and unique definitions in mathematics.

 

Tables created:

 

Example 1:

Here I create a real, float, decimal for the table. The field type I create is decimal (3,1), the length is 3, and the decimal is 1

The queried data follows the rounding. Here I insert 88.04, so the queried data is 88.0

 

Example 2:

Here, I insert 88.05 into the decimal field, and the query data is 88.1

 

Example 3:

Let’s test what happens when the length exceeds the limit. The decimal places in the table are reserved by one digit. Therefore, no matter how many digits you enter, the decimal places are reserved by one digit and no error will be reported. However, if the length exceeds the limit, an error will be reported

Arithmetic overflow error converting varchar to data type numeric.

 

Two pieces of data are inserted according to the above example, and the result can be obtained: