Thought Bubble #1 – Float and real in SQL Server

float and real numbers

Did you know what the thought bubble returns?

Sometimes, when you don’t know what’s real anymore, and you go to your friend, SQL Server to find out…you may see that he doesn’t know either.  You may think that you entered a very specific number and you may want it back the same way at a specific point in time, but then you find out that everything’s changed and you just wonder WHYY?

OK, let’s see why this is happening.

According to MSDN, float and real are special data types, mostly known for the fact that they are imprecise types: “Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. The ISO synonym for real is float(24).”

On TechNet, we are even advised to avoid using these data types in the WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

My personal opinion is that we should avoid using float and real data types, mostly when we have to deal with more sensitive data. They may bring a performance improvement when we have large quantities of data to load or process, because they don’t consume a lot of storage space ( 4 bytes for REAL and 8 bytes for FLOAT) , but they can generate mode difficult problems afterwards, which can be very hard to debug.

 

Did you have any challenges when working with float and real data type? Please If so, please share your experience with us, in the comments below.