Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Exact and Approximate Expand / Collapse
Author
Message
Posted Thursday, January 5, 2012 6:05 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, May 28, 2014 9:45 PM
Points: 26, Visits: 108
SQL Kiwi (1/5/2012)
[quote]
Interesting. The documentation looks very similar between ASE 15 and SQL Server:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/blocks14.htm
http://msdn.microsoft.com/en-us/library/ms190476.aspx

The magic number 6 appears for division in both, but not multiplication.

Interesting find ... however we have to read the Sybooks fine print ...

"For numeric and decimal datatypes, each combination of precision and scale is a distinct Adaptive Server datatype."

I only noticed this because the chapter heading is "Datatypes of mixed-mode expressions" and I thought ... hold on a minute, the QOTD is not mixed mode because the 2 multiplicands are identical scale and precision!!! Therefore the import of Table 1-3 does not apply to the QOTD for Sybase.

The MSDN entry is a bit more cryptic and harder to interpret as to whether the concept of mixed mode applies for numeric same scale and precision. The writers are fixated with decimal ranking higher than numeric and remain silent on the finer points.

Regardless I guess one can infer from the QOTD outcomes that SQL Server applies the 6 digit guillotine universally?!?
Post #1231218
Posted Thursday, January 5, 2012 8:02 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 27, 2014 7:17 PM
Points: 197, Visits: 459
I got it wrong, though I have always maintained that binary representations are more accurate than decimal ones (which they are of course) .

Sounds to me like the real problem here is that SQL SERVER treats DECIMALs too much as a strings rather than numbers. Treating them as strings rather than numbers even when you are doing arithmetic on them (the only reason why you "need" the absurdly high "precision" and get the resulting truncation issue in the first place) really indicates a lack of understanding the difference between the semantics and the representations of the data type.
Because there is nothing to stop a number representation based on the decimal system (instead of binary) to also use a floating decimal point. Nor is there any good reason (assuming you want to preserve the database representation for historical reasons) why SQL Server couldn't convert the disk-based strings into a format that treats the intended numbers as a numbers when it needs to manipulate them.

So it's a (documented) bug in my book.
Post #1231231
Posted Monday, January 9, 2012 9:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:38 AM
Points: 7,700, Visits: 9,428
SQL Kiwi (1/5/2012)
IIRC that's the same implementation used by the .NET framework. It would make a good Connect suggestion anyway. If you do enter one, link back here and I'll vote for sure.

I've raised a connect item.


Tom
Post #1232524
Posted Monday, January 9, 2012 9:07 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 9,923, Visits: 11,170
L' Eomot Inversé (1/9/2012)
SQL Kiwi (1/5/2012)
IIRC that's the same implementation used by the .NET framework. It would make a good Connect suggestion anyway. If you do enter one, link back here and I'll vote for sure.

I've raised a connect item.

Voted




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1232535
Posted Tuesday, January 24, 2012 2:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:14 PM
Points: 12,966, Visits: 10,741
Great question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1240674
Posted Thursday, February 9, 2012 4:08 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, November 5, 2013 6:08 AM
Points: 1,079, Visits: 591
Nyc Q tx....


What you don't know won't hurt you but what you know will make you plan to know better
Post #1249600
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse