THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

Interesting Bug/Feature In SQL Server 2008 RTM

Someone had a problem with 8 year old procs which started to fail after moving to SQL Server 2008
Of course he should have used ints, but let's see what happens

Run this code on SQL Server 2005 and 2000

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = -1;

SELECT @num_Passed


IF (@num_Passed = 0)

PRINT 'True';


No problem right?

Run just this part on SQL 2008

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = -1;

SELECT @num_Passed

No problem either
Now run this whole thing

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = -1;

SELECT @num_Passed


IF (@num_Passed = 0)

PRINT 'True';

Oops, this is what we get
Server: Msg 8115, Level 16, State 2, Line 7
Arithmetic overflow error converting expression to data type tinyint.

Change the -1 to 1

DECLARE @num_Passed Numeric(2, 0);

SET @num_Passed = 1;

SELECT @num_Passed


IF (@num_Passed = 0)

PRINT 'True';

No problem either.

Run this

IF (convert(Numeric(2, 0),-1) = 0)

PRINT 'True';

That fails
Let's make it numeric(3,0)

IF (convert(Numeric(3, 0),-1) = 0)

PRINT 'True';

No problem, that runs fine. So is this a bug because of implicit conversion to tinyint which can't hold negative values?

Published Monday, August 25, 2008 12:28 PM by Denis Gobo
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



AaronBertrand said:

August 25, 2008 12:32 PM

brejk said:

Unfortunately this bug is still not fixed. Moreover, run this on SQL Server 2008:

select sql_variant_property(cast(0 as sql_variant),'BaseType')

and check if the result is... int :-) Where is the tinyint???

And of course tinyint and int are both lower in the type precedence than numeric... How the hell can I recommend SQL Server 2008 to my customers? ;-)

December 31, 2008 3:53 AM

rsocol said:

This problem is fixed in SQL Server 2008 SP1 CU1 and in SQL Server 2008 RTM CU4 (see KB 961695).

May 31, 2009 2:20 PM

Ponnemparambatha Vijaya Raj said:

select count(jwmasterforlist.transid) from jwmasterforlist, jwworkorderform

is giving

Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.

i want this problem to be solved so that my programs can work smoothly how to convert the result to bigint for count. i tried all combination but same results are coming that count is not a function. if anyone has a result please send it to which is our development teams email ID. you can visit our website to to know more about our programs which are being executed at a very large scale.

If any one has any queries then we can clarify the same on it.

other results do not have much problem because they can be converted. in case of count function it is not so. we have large applications built on this function and cannot be changed.


November 28, 2015 12:51 AM

Leave a Comment


About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog


Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement