When a decimal isn't a decimal - beware
To say the type system in SQL is lax is an not quite
correct, its actually lax, in consistent and very annoying.
The most common feature I come across is that of integers and decimals.
If I declare a variable as decimal(10,4) and assign it the value 10/4 you
could expect a number of things to happen
1. The code doesn't run because you are casting an integer to a decimal
2. The code runs and stores 2.5 in the variable
3. The code runs and does the calculation based on integers 10/4 and
realises truncation will occur (as the destination type is decimal) and raise a
warning
4. The code calculates the 10/4 as integers so results in an integer of 2 and
sticks it in the variable.
In a nice type safe system, i.e. c# one would get 1. The result of an
integer/integer is an integer and you can't store an integer in a decimal. This
is nice because to get the value stored you have to explicitly do a cast. This
makes you think and realise that you probably have to cast the 10 or the 4 to
get the required decimal value of 2.5 returned.
In TSQL however you get 4.
If you want to look at inconsistencies then look at ISNULL and COALESCE.
ISNULL returns the same type as the first parameter and COALESCE returns
the data type based on evaluating all the data types passed to
the function and evaluating the top datatype
declare @i int
declare @s varchar(100)
select ISNULL(@s,'100') + '100', ISNULL(@s, 100) + '100'
,COALESCE(@s,'100') + '100', COALESCE(@s, 100) + '100'
This is sort of where I got stuck today, I was using the Radians function to
use in a conversion from longitude and latitude to OS grid reference. This is a
shocking formula and one that I have javascript and C# versions but not a TSQL
one. I couldn't for the life of me figure out why the TSQL result was vastly
different.
So I debugged the code, thank you SQL 2008 (although I did find a nice
feature I will blog about later).
What I found was this line wasn't returning the same value in TSQL as in C#,
it was returning 0 in TSQL, and even I know 49 degrees isn't 0 radians
declare
@lat0 numeric(29,15)= radians(49)
Gosh I thought I had found bug in TSQL. Then I had a euraka moment, you're
probably already there. I changed the code to
declare
@lat0 numeric(29,15)= radians(49.0)
and it returned the correct value. On looking at BOL it does say that radians
returns the same data type as the value passed. Which to me sounds mad because
the most even for a value of 360 degrees the radians value is only 6 and so the
decimal places are very very important. Whats more any value of degrees
between 0 and 90 is < 1 and so if you only specify an integer for the
degrees, as in my case above, you will get 0.
Anyway lesson learnt. So from now on as well as always changing 10/2 to
10.0/2 I will be making sure any literals passed to functions like this will
also have .0 tagged on the end.
-