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.


-
Published 26 May 2009 14:07 by simonsabin

Comments

27 May 2009 08:11 by GBN

# re: When a decimal isn't a decimal - beware

On SQL Server 2005 and earlier it works.

DECLARE @foo decimal(10,4)

SELECT @foo = 10/ 4

SELECT @foo

I assume you mean SQL 2008?

The ISNULL and COALECSE is well known and goes back to the T-SQL vs ANSI-SQL

27 May 2009 09:57 by GrumpyOldDBA

# re: When a decimal isn't a decimal - beware

It's always been like that as far as I know and it does make sense if you think of it this way. If I do integer mathematics do i expect integer results or floating point results -- OR -- should sql server be able to guess what i want and adjust accordingly?

I'd expect integers from integers and floats(dec) from decimals otherwise you'd have all sorts of issues with overflows etc. If I remember back to my C++ days didn't we resolve this with something called overloaded functions? it's a long time ago if I've got the names wrong!

Anyway I agree it's annoying when it takes a while to work out why your code doesn't work - but I'd say logically it was correct that you should explicitly define your data types to return the result you want, otherwise we'd end up getting to defining varchar(max) for all our character data just in case we needed more space - imagine a number(max) data type which could hold any number but size to suit - be chaos.

27 May 2009 10:30 by simonsabin

# re: When a decimal isn't a decimal - beware

GBN

Note sure what you get but on all editions I've tried I get the value 2. Which is not the answer of 10/4 in my book.

I didn't say it wouldn't run, its just annoying that I have trucation occurring because I've forgotten the .0 and there is no way of catching this at compile time.

GrumpyOldDBA

The compiler should be able to tell me I am probably doing something wrong because I am trying to stick an integer in a decimal variable. Erland has a suggestion for enabling this www.sommarskog.se/strict_checks.html

A bit like in VB.

27 May 2009 11:33 by GBN

# re: When a decimal isn't a decimal - beware

The right hand side expression is evaluated before assignment. On assignment it will cast to decimal. Before assignment it is integer arithmetic.

The same would happen in .net too: it's simple operator precedence and "/" comes before "=" in most languages.

27 May 2009 18:48 by simonsabin

# re: When a decimal isn't a decimal - beware

I'm clearly talking out of my behind. For some reason I thought that you couldn't implicitly cast in C# but it looks like you can

msdn.microsoft.com/.../aa691282(VS.71).aspx

RTFM Simon

27 May 2009 18:50 by simonsabin

# re: When a decimal isn't a decimal - beware

I still think it would be good to be able to catch this stuff at compile time and not as a result of someone notincing invalid data being generated.

28 May 2009 10:14 by GBN

# re: When a decimal isn't a decimal - beware

I agree the Erland's "Option strict" would be ideal to remove these ambiguities...

# When a decimal isn&#8217;t in TSQL &laquo; Golbing

Pingback from  When a decimal isn&#8217;t in TSQL &laquo;  Golbing

# Weekly Link Post 96 &laquo; Rhonda Tipton&#8217;s WebLog

Pingback from  Weekly Link Post 96 &laquo; Rhonda Tipton&#8217;s WebLog