Fugato

SQL Server is sometimes fine, and sometimes a harbinger of evil.

You’d think you could just insert whatever into it and if you give it something it can’t handle, it would belch an appropriate error message back at you, being the enterprise-class software it is. Right?

Or at least, you’d think that if you were to find a case of bogus data that you could feed SQL Server, that it would incorrectly accept and then go corrupt, then that case should be a pretty obscure, oddball case, since SQL Server would of course be tested for all the typical cases … being the enterprise-class software it is. Right?

You certainly wouldn’t expect that case to be a simple NaN (not-a-number) value, would you?

Well, try inserting Double.NaN into a float column using the .Net System.Sql API. Observe how it is merrily accepted, after which:

• simple queries involving that column (or just *) will fail or return partial result sets
• queries with aggregates in them will fail with “Error 3628: A floating point exception occurred in the user process. Current transaction is canceled.”
• Query Analyzer will silently ignore these rows in any query that involves reading that column
• An Enterprise Manager query will manage to display the bogus rows (with -1.#IND shown for the bogus value), but trying to change them manually results in an error saying another user has updated the row so your view of it is out of date

Once the bogus value is in there, the only remedy I’ve found is to run an UPDATE tblName SET floatCol = @someOkValue WHERE id IN (x,y,z, ...) where I fill in the IN list by hand by eyeballing the bad rows in Enterprise Manager.

Sure, inserting a Double.NaN into SQL server may be an error … but this is no way to handle an error.

I am now manually checking Double.IsNaN() and throwing an exception on true, before every assignment to a SqlDbType.Float parameter in my code. Ojbara.