ZeroFusion (11/24/2009)
As for the length constraint - according to the MS SQL Data Types list, "Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308". Such numbers have 309 digits - hence the limitation.
...
So numbers between 1.79E + 308 and 2.23E + 308 seem not to be taken into account.
There's a typo in BOL2000. The correct range is "-1.79E + 308 through -2.23E - 308, 0 and 2.23E - 308 through 1.79E + 308".
ISNUMERIC() returns a 0 against any string longer than 309 characters, even if it is numeric.
Is this considered to be an explanation? This is completely incorrect. There are many 310-, 311-, and even 600-character long strings for which ISNUMERIC returns a nonzero value, for example:
SELECT
ISNUMERIC('-' + REPLICATE('1', 309)),
ISNUMERIC('+' + REPLICATE('1', 309) + '.'),
ISNUMERIC(REPLICATE('1', 300) + '.' + REPLICATE('1', 299))
These strings can be converted to the float data type. So ISNUMERIC() returns 1 as a result.
Please correct the explanation... ZeroFusion said about data type conversions - that's the reason.
Anyway, thanks to the author for making me to do some investigation about SQL Server data types and data type conversions. It was quite interesting 🙂