In the article, we find out and learn how numeric or decimal values are represented as FLOAT and REAL data types in database tables. Providing examples of how to embed FLOAT and REAL data types in a table, filling in and calculating the values between these data types.
The right data type is important when given to every table column in a database by beginners or even more experienced database developers. Miscalculation can occur when a column represents a number that contains a decimal value.
Syntax for the FLOAT vs. REAL data types
There are SQL Server data types that represent numbers that are not whole integers. Those are such as decimals, numbers, money, small money, float, and real. FLOAT and REAL are approximate data types with floating-point numeric values.
Let’s take a look at the following syntax of the FLOAT and REAL data types in T-SQL:
ColumnName FLOAT [(N)] ColumnName REAL
The REAL data type is considered as a single-precision floating-point number that has 4 bytes in length with 24 digits of precision to the right of the decimal point. The FLOAT data type has a length and precision based on a specified N value, and the standard is 53. The following table shows the comparison between FLOAT and REAL data types.
Type | Storage Size (Bytes) | Precision
(Digits) |
Value of N | Minimum Range | Maximum Range |
REAL | 4 | 7 | – | -3.40E+38 to -1.18E-38 | 1.18E-38 to 3.40E+38 |
FLOAT | 4 | 7 | 1 – 24 | -1.79E+308 to -2.23E-308 | 2.23E-308 to 1.79E+308 |
8 | 15 | 25 – 53 |
Table 1. The comparison of FLOAT and REAL data type
The REAL data type is a synonym of FLOAT(24). When we specify N values of FLOAT data types with 24 or lower, it will be stored as a REAL data type. The rest will remain stored as FLOAT according to the value of N.
Examples
Here is a table named myTB in the MYDB database. This table has two columns consisting of the FL column using the FLOAT data type, while the other is the RL column using the REAL data type.
Example #1 Creating the myTB table using FLOAT and REAL column types
We create a database named MYDB through Transact-SQL and then the myTB table with FL and RL columns. The FL column uses a FLOAT data type while the RL column represents a REAL data type. The following syntax is used for creating them.
CREATE DATABASE MYDB; USE MYDB; GO CREATE TABLE myTB ( FL FLOAT, RL REAL ); GO
To see the information on the table above that we just created, we can describe the myTB table by executing the sp_help stored procedure followed by the table name. The syntax is as follows:
EXEC sp_help myTB; GO
The result will appear:
Figure 1. The myTB table description
Example #2 Adding records into FLOAT and REAL column types
We insert several records containing positive and negative values in integers and decimals into the myTB table. Each inserted row will have the same value between the FL and RL columns. Let’s look at the following syntax:
INSERT INTO myTB (FL, RL) SELECT 1, 1 UNION SELECT -1, -1 UNION SELECT 0.1, 0.1 UNION SELECT -0.1, -0.1 UNION SELECT 1.00000009, 1.00000009 UNION SELECT -1.00000009, -1.00000009 UNION SELECT 10000000.9, 10000000.9 UNION SELECT -10000000.9, -10000000.9
To see the data in the myTB table, let’s execute the “SELECT * FROM myTB” syntax. The result will appear as follows:
FL | RL |
1 | 1 |
-1 | -1 |
0.1 | 0.1 |
-0.1 | -0.1 |
1.00000009 | 1 |
-1.00000009 | 1E+07 |
10000000.9 | -1 |
-10000000.9 | -1E+07 |
Table 2. Records inserted in the myTB table
Above table in the blue row shows the difference values between the FL column with the FLOAT data type and the RL column with the REAL data type when stored in the myTB table.
Example #3 Calculate the FLOAT vs REAL values
Now, we want to do calculations for FLOAT and REAL values in the myTB table. We calculate using the SUM() function in the FL and RL columns which have values 1 and 0.1. See the following syntax:
SELECT SUM(FL) AS FLSUM, SUM(RL) AS RLSUM FROM myTB WHERE FL IN (1, 0.1) OR RL IN (1, 0.1)
The result will appear:
FLSUM | RLSUM |
1.1 | 1.10000000149012 |
Table 3. Results for the FLOAT vs REAL values
The expected value of the calculation results is 1.1. As we have seen above, the FLOAT type shows the corresponding value while the REAL type shows an inaccurate value.
Instant livechat to an Expert!
Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service!
Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Reply