Today, I'm continuing the discussion on the variety of data types supported by SQL Server. Last time I mentioned exact decimal numbers, but there are also inexact (or approximate) decimal numbers. Here are the available types:
These two data types float and real are approximate data types.
You probably realize that in decimal, there are values we can't store exactly like 1/3. No matter how many 3's we write when we write 0.33333333, we are still never going to have the exact value.
And the same happens in binary, just with different numbers. A simple example is ten cents. A value of 0.1 can't be stored exactly in float or real.
For that reason, they should only rarely ever appear in business applications. Using them for things like amounts of money is a really common newbie mistake when working with SQL Server.
There are places where float and real make sense but they are mostly in scientific and technical applications.
It's worth your while becoming proficient in SQL. If you'd like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.
5 thoughts on “T-SQL 101: #38 Approximate numbers in SQL Server”
I have to store measured values in SQL Server.
The precision can vary. So it's important that e.g. measurement of 1.9 ist stored as 1.9.
The value 1.90 would have an other meaning, namly that the precision really was 2 digits.
Float does not work, because 1.90 will be changed to 1.9
Decimal does not work, because 1.9 will be changed to 1.900… depending on the decimals
What would you recomend here?
Hi Claus, I don't think you have any choice but to store it as a string, much as that hurts me to say 🙂 You might also want to store it in a decimal form as well, to be used in calculations, as there's no difference between 1.9 and 1.900 in this case in a calculation.
thank you for your answer!
You are most welcome
One other thought: You could also put it in decimal and record the precision in another column.