Exact and Approximate

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul White » Exact and Approximate

56 posts, Page 6 of 6

«««2 3 4 5 6

Exact and Approximate

Rate Topic

Display Mode

Topic Options

Author

Message

Frank Hamersley

Posted Thursday, January 05, 2012 6:05 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 10:21 PM
Points: 24, Visits: 100

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

Alex Fekken

Posted Thursday, January 05, 2012 8:02 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 7:50 AM
Points: 195, Visits: 444

I got it wrong, though I have always maintained that binary representations are more accurate than decimal ones (which they are of course) Crying

.

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

L' Eomot Inversé

Posted Monday, January 09, 2012 9:02 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 7,102, Visits: 7,166

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
Que conclure à la fin de tous mes longs propos? C'est que les préjugés sont la raison des sots. (Voltaire, 1756)

Post #1232524

Paul White

Posted Monday, January 09, 2012 9:07 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 1:02 AM
Points: 10,989, Visits: 10,534

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