November 26, 2007 at 11:18 am
max (11/26/2007)
Guys, this is driving me crazy!
Your code dump is driving me crazy too.
It's incomplete.
I don't understand it at all.
Please try to be a little clearer.
Look at my code dump. Look at Jeff's code dump.
You can cut/paste/execute it and get a result that bears directly to the problem you are presenting.
This is not true with your code dump.
With your code dump I don't understand what you are trying to show - at all.
November 27, 2007 at 5:48 am
well, it seems that the difference (tsql = 0 and vb = -1.2325951644078309E-32) is due to difference round between sql and vb.
who I can increase round to have the save values?
T-SQL
DECLARE @Tmp FLOAT(53)
DECLARE @tmp2 FLOAT(53)
DECLARE @m FLOAT(53)
SET @Tmp = 2.7755575615628914E-17
SET @tmp2 = 7
SET @m = 3.9650822308041303E-18
SELECT @Tmp + (@tmp2 * -@m) AS 'NEW VALUE'
NEW VALUE = 0.0
VB6
Dim Matrix As Variant
Dim m As Double
ReDim Matrix(1, 0) As Double
Matrix(0, 0) = 7
Matrix(1, 0) = 2.77555756156289E-17
m = 3.96508223080413E-18
Debug.Print Matrix(1, 0) + (Matrix(0, 0) * -m)
-1.23259516440783E-32
If you copy the value used in VB to TSQL u get the same result.
November 28, 2007 at 9:41 am
Max,
Your code dumps are much, much better.
It seems that you relish living in this twilight zone that sits in the world between 15 and 16 significant digits of double precision numbers.
Apparently SQL Server doesn't complain about the fact that you supply 17 digits. I don't know why. If you display @Tmp and @m you get all original 17 digits. But this is not true if you increase the last digit of @Tmp and @m by 1!
When I load these variables into a table and access them via VB/ADO the extra digits get promptly truncated and the result of your operation is zero - just like in SQL Server.
There is a never ending series of surprises on the VB side - all having to do with screwing around on that 15/16 edge.
Let me show what you are doing with a more intuitive example. The closest star to us is Proxima Centauri at 4.2 light years. The speed of light is 299,792,458 meters/second. A year has 31,536,000 seconds. Thus Proxima Centauri is 39,707,870,813,049,600 meters away from us (How do you pronounce this, i.e. what comes after trillion; is it a zillion?). You have two measuring devices which make two measurements of the distance (after waiting about 8.4 years) and you stick it into a SQL Server script like so:
declare @m1 float(53)
declare @m2 float(53)
set @m1=39707870813049600
set @m2=39707870813049601
select @m1,@m2,@m1-@m2
This will get you ZERO - even though you and I know that is wrong - because you are in that twilight zone.
But if you have two measurements like so:
declare @m1 float(53)
declare @m2 float(53)
set @m1=39707870813049600
set @m2=39707870813049650
select @m1,@m2,@m1-@m2
This will get you -48.0. Close but no cigar - because you are still in that twilight zone.
Hope this makes it a bit clearer.
By the way, the Windows Calc program produces the correct result for both operations. Obviously it uses more than 64 bits - at the software level.
November 28, 2007 at 12:40 pm
How do you pronounce this, i.e. what comes after trillion
Heh... "Quadrillion"...
Mill = 1
Bill (or BI) = 2
Tri = 3
Quad = 4
etc, etc...
Except in some European countries 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
November 29, 2007 at 1:30 am
Good! I learn to post code in messages in the right way!
You code shows sql server behavior very well.
Unfortunately I need to have the same value in t-sql, not way to change customer's specification on this :angry:
I think t-sql is not the right choice to do such a thing, what is your opinion?
Thank you all for the time you are spending on this.
Regards,
max
November 29, 2007 at 11:02 am
It's not a question of T-SQL or VB. It's a question of double precision numbers(1/11/53) not being able to deal with the level of detail you need. I have no intention of changing 'the specification'. The numbers you deal with are at the level of the diameter of an electron. But the problem doesn't change when you go to the opposite end of things, i.e. very large distances. That was my point.
And getting back to the Windows Calc program. It can easily perform the following calculation without losing a bit:
[font="Courier New"]
+39707870813049000.7894342
-39707870813049000.7611991
=================0.0282351[/font]
On the other hand, if you do
[font="Courier New"]+0.0000000000000027755575615628914
-0.00000000000000277555756156289121[/font]
both operands get truncated to
[font="Courier New"]=0.000000000000002775557561562891[/font]
and you get zero.
So Windows Calc appears to accept no more than 31 'significant' digits (i.e. digits you type).
I'll leave it up to you to discover its twilight zoneS (yes it may have two - or as many as 31).
But it shows that your problem can be solved with software.
Cheers.
November 29, 2007 at 2:51 pm
I'm sure you must know this already, but the internal storage for a TSQL Real is 4 bytes. It's the equivalent to a C/C++ float or a VB6 "Single".
The TSQL float is stored in 8 bytes, it's equivalent to a C/C++ double or a VB6 Double.
Given your need for high precision, is there a reason why you'd not use the TSQL Float type?
Obviously it consumes twice as much storage, but from an execution speed perspective, it makes no difference - the actual size of the CPU floating point registers is 80bits (larger than the 64 bits used for TSQL float).
With regard to the associated BOL information, I tried the following:
Select @Rx = 3.4E-38
Select @Rx
and got
3.3999999E-38
I also tried:
DECLARE @fX FLOAT, @fY FLOAT, @fM FLOAT
SELECT @fX = 2.77555756156289E-17, @fY = 15, @fM = 1.85037170770859E-18
SELECT convert(real, @fX + ( @fY * -@fM ))
and got 5.2385294E-32
Here, the calculations are done using 8 byte FLOATs, but the final answer is stuffed back into a 4 byte real.
John
Viewing 7 posts - 31 through 36 (of 36 total)
You must be logged in to reply to this topic. Login to reply