Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

The Difference Between FLOAT vs REAL Data Type in SQL Server – Querychat

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

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc