.Net Float vs MS SQL Float

While working on some custom tracking software, I ran into a very peculiar issue when storing some floating point values in a Microsoft SQL database. Specifically, I noticed I was getting weird rounding applied to my input. This only happened when there were more than two digits after the decimal point; for instance, 1.005 would become 1.004853184548342. The column in MS SQL was a float, and I was using a float before sending it through the ADO.Net pipeline.

Well it had to be my code right? Maybe a conversion error, or some other weird issue with the number while it was being interchanged from the front end to the object then finally to the database. Using debugging, I watched the number progress through each step of my code, always with a correct floating point number (once the initial conversion from a text field was done), always 1.005. As soon as it hit the database, the problem cropped up; reading the value back out of the database I was returned the crazy long number instead.

So I’m pulling hair, trying to figure this mess out. Turns out MS SQL is telling a bit of a fib! A float (or 32-bit number) column in MS SQL is really a double (or 64-bit number)! When MS SQL was receiving my float, it had to cast it into a double, increasing its precision. Why didn’t it just add a bunch of zeros? Only Microsoft knows. The very simple fix is to use a double variable in .Net when using a MS SQL float column.

Normally, you should always use the same variable type (string to string, int to int) anyway. But for MS SQL and .Net, it’s Float to Double!

For reference, I was using Microsoft SQL Server 2008 and Visual Studio 2010 (.Net 4.0). Both applications and the underlying operating system were 32-bit.