What is the Difference between NUMERIC / DECIMAL and FLOAT / REAL

Q. What is the Difference between NUMERIC / DECIMAL and FLOAT / REAL in SQL Server 2012?

Ans:

Before dig into these data types we must have a look into precision and scale.

Precision: The maximum total number of decimal digits that can be stored both to the left and to the right of the decimal points.

Scale: The maximum number of decimal digits that can be stored to the right of the decimal point

Example:

Take an example number: 1433890.2554

Here the precision is: 11 (1433890)

Scale is: 4 (2554)

To store this number into a variable it must be declared with the proper precision and scale.

USE udayarumilli;

GO

DECLARE @Test DECIMAL(11,4) = 1433890.2554;

SELECT @Test

Output is:

—————————————
1433890.2554

(1 row(s) affected)

From the above example if we change the precision value to 10 and try to assign the same number the execution will be failed with “Arithmetic Overflow error”.

USE udayarumilli;
GO
DECLARE @Test DECIMAL(10,4) = 1433890.2554;
SELECT @Test

Output would be:

Msg 8115, Level 16, State 8, Line 1
Arithmetic overflow error converting numeric to data type numeric.

(1 row(s) affected)

As we have changed the precision value to 10 it turns into arithmetic overflow error.

Now we’ll have a look into these data types.

NUMERIC / DECIMAL: These data types are the similar in functionality and used to represent the fixed precision and scale values.  These are most apt for the situations where we need to represent accurate values that include “Price”, “Balance”, and “Credit” etc. Please have a look here to know more about these data types.

 REAL / FLOAT: These data types can be used when accuracy is not a deal. To represent the very large numbers or very small numbers that includes scientific purposes and average counts, approx. values etc. Please have a look here to know more about these data types.

We’ll demonstrate an example, and we’ll see how these data types are varying in handling the same number.

Declare a variable of type FLOAT and try to convert it into NUMERIC.

DECLARE @Var_F FLOAT = 1234567.02265;

SELECT  @Var_F AS 'Float_Value',

        CAST(@Var_F AS NUMERIC(20,10)) AS 'Numeric_Converted';

Can you guess the output of converted value?

Float_Value            Numeric_Converted

———————- —————————————

1234567.02265          1234567.0226499999

(1 row(s) affected)

You can clearly observe the difference between numbers.  This is the reason we should not use the columns of type Float while comparing with the other values in a table.

Means we should be very careful using these columns and conversions in WHERE clause.