views:

91

answers:

2

Using a TSQL update command against a SQLServer database, how can I update a column of type FLOAT with the smallest possible double value?

The smallest possible double value in hex notation being 3ff0 0000 0000 0001 (http://en.wikipedia.org/wiki/Double%5Fprecision)

A: 

Don't, use decimal to avoid losing the value. Or binary(8) if you want to store it in hex, as per the article.

Seriously: depending on the CPU, the wind direction and moon phase, it's highly likely won't get the same value when you manage to convert it.

And what do you mean by "smallest"? 3ff0 0000 0000 0001 = 1.0000000000000002

And, is it hex in the client but you want float in the db?

gbn
A: 

Whatever it is you need this for, I suggest you consider alternatives that don't require assumptions about SQL Server's FLOAT type. Unfortunately, SQL Server is rather flaky about IEEE 854 compliance. For example, see these newsgroup threads. Also note that SQL Server's behavior in this regard has changed between versions and may well change again without warning. Without giving all the gory details, the smallest value you can assign directly to a FLOAT is not necessarily the smallest value a FLOAT can contain (without complaining). Some of SQL Server's flakiness also revolves around what IEEE calls "denormalized" floating point numbers, consideration of which is important if you want "smallest" to have a precise meaning.

Sorry not to answer your question, but I don't think much good can come from answers that help you head further down the rocky path you're on.

Steve Kass