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

udayarumilli_decimals

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.

 

 

Posted in SQL Development | Tagged , , , , , , , | 3 Comments
Subscribe
Notify of
guest
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
trackback
The Last Driver cheat
10 years ago

My brother recommended I might like this blog. He was totally right. This post actually made my day. You cann’t imagine simply how much time I had spent for this information! Thanks!…

hack
hack
10 years ago

Thank you for sharing superb informations. Your site is very cool. I am impressed by the details that you have on this site. It reveals how nicely you perceive this subject. Bookmarked this web page, will come back for more articles. You, my friend, ROCK! I found just the info I already searched all over the place and just couldn’t come across. What a perfect site.

maillot pays bas home
maillot pays bas home
10 years ago

Many thanks a whole lot for sharing! I will definitely be back.