SQL Server Math

[baavgai]

In Microsoft's SQL Server, select 7/4, 7.0/4 tells us that:

7/4 == 1
7.0/4 == 1.75

That is all...

Ok, that not all. This little quirk bit me, hard. It should be explained.

Basically, all constant values have an assumed data type. The value 7 is assumed int, 7.0 assumed float. If all elements are int, then the desired result is assumed to be int. And if you're doing math with variables that are all int? You have to play cast games.

declare @n int, @d int
select @n=7, @d=4
print @n/@d -- yields 1
print cast(@n as float)/@d -- yields 1.75
print cast(@n/@d as float) -- yields 1, cast too late

I suppose I wouldn't mind this as much if select 'Value=' + (7.0/4) didn't give Error converting data type varchar to numeric.. Value assumptions are nice, but they should be consistent. I'd much prefer the assumptions never presumed I actually wanted to loose precision, like the fraction rounding does.

Comments

[beldon.livejournal.com]

I hear ya, man! Used to be a SQL Server guy years ago and I ran into these kinds of things all the time. Parser bugs, memory leaks, you name it.

Microsquish's Excel math has had lots of problems which I've heard tell only get worse with this Office XML format they have now.

[baavgai.livejournal.com]

Thankfully, as DBA I've managed to excise a lot of the home grown Excel and Access monsters in favor of client server gremlins. As a result, I haven't had to gaze into spreadsheet land for some time. Still, I hear bad things about the 2007 suite.

Microsoft's XML is a joke and always has been. It's worst kind of tech, playing to the gods of Buzzword and PR, please don't invoke a call to Substance lest you banish the carefully crafted illusion. That illusion is propped up on a telephone book size spec.

This guy actually took the time to read the spec. Yeah, it's crap. (http://www.robweir.com/blog/2007/07/formula-for-failure.html)