SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Exact and Approximate


Exact and Approximate

Author
Message
Frank Hamersley
Frank Hamersley
SSC-Enthusiastic
SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)SSC-Enthusiastic (104 reputation)

Group: General Forum Members
Points: 104 Visits: 110
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?!?
Alex Fekken
Alex Fekken
Old Hand
Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)Old Hand (385 reputation)

Group: General Forum Members
Points: 385 Visits: 460
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.
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26355 Visits: 12506
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

Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36472 Visits: 11361
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Koen Verbeeck
Koen Verbeeck
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64304 Visits: 13298
Great question, thanks.


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

MCSE Business Intelligence - Microsoft Data Platform MVP
BudaCli
BudaCli
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1570 Visits: 598
Nyc Q tx....
;-)

What you don't know won't hurt you but what you know will make you plan to know better
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search