February 19, 2012 at 7:19 am
select (cast ('1' as smallmoney) * 100000)
select (cast ('1' as smallmoney) * 1000000000)
select (cast ('1' as smallmoney) * 10000000000)
Output:
(1 row(s) affected)
Msg 220, Level 16, State 3, Line 2
Arithmetic overflow error for data type smallmoney, value = 1000000000.
(1 row(s) affected)
I am wondering why this error only with 2nd SELECT !! (and it works with smallest and biggest and not the in-between)
Always appreciating your help.
February 19, 2012 at 8:51 am
smallmoney has limits ! - 214,748.3648 to 214,748.3647
The engine will try to return a result in the same data type.
ref:http://msdn.microsoft.com/en-us/library/ms179882.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 19, 2012 at 8:53 am
I agree with Johan - what calls my attention is not that second statement fails (which is expected) but that third statement shouldn't work and apparently it does work.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.February 19, 2012 at 9:23 am
ALZDBA (2/19/2012)
smallmoney has limits ! - 214,748.3648 to 214,748.3647The engine will try to return a result in the same data type.
1. Regarding Limits:
Yes. i thought this, but i surprised when i tried the below query for TINYINT, as it worked (thought its limit is 255)
select cast('1' as tinyint) * 1000000000
2. Even if we assume 'Limit' is the problem, then how come 3rd Statement (of my question)works !!
Always appreciating your helps.
February 19, 2012 at 11:24 am
It has to do with type precedence and what happens when you add different data types together.
In the first, you're adding a smallmoney and an int and getting a result of type smallmoney.
In the second, the same and the result is too large to fit into a smallmoney, hence the error.
In the third, you're adding a smallmoney and a numeric(11,0) (the second value is too large for an int) and getting a result of type numeric(22,4), the resultant value easily fits into a numeric(22,4), so no error.
Same thing happens with the tinyints.
select cast('1' as tinyint) * 1000000000 works because tinyint * int = int (must be the larger data type)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 19, 2012 at 12:02 pm
of course, Gail hit the nail on the head once more 🙂
ref in BOL: Data Type Precedence (Transact-SQL)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 20, 2012 at 4:58 am
GilaMonster (2/19/2012)
It has to do with type precedence and what happens when you add different data types together.In the first, you're adding a smallmoney and an int and getting a result of type smallmoney.
In the second, the same and the result is too large to fit into a smallmoney, hence the error.
In the third, you're adding a smallmoney and a numeric(11,0) (the second value is too large for an int) and getting a result of type numeric(22,4), the resultant value easily fits into a numeric(22,4), so no error.
Same thing happens with the tinyints.
select cast('1' as tinyint) * 1000000000 works because tinyint * int = int (must be the larger data type)
Gail, I have 2 Questions from your answer:
1. if with 3rd SELECT, the big value was recognized with bigger datatype (here numeric), but why this did not happened with 2nd SELECT?
2. You have mentioned ;
smallmoney * int = smallmoney
and
tinyint * int = int
is there any chart (or a link) where from i can get to know such formula for different data type combination?
February 20, 2012 at 5:02 am
DBA Rafi (2/20/2012)
1. if with 3rd SELECT, the big value was recognized with bigger datatype (here numeric), but why this did not happened with 2nd SELECT?
Because the second value fitted in an int, in the 3rd select, the second value would not fit into an int.
2. You have mentioned ;
smallmoney * int = smallmoney
and
tinyint * int = int
is there any chart (or a link) where from i can get to know such formula for different data type combination?
The link that Johan posted yesterday?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 20, 2012 at 2:06 pm
The 2nd example will work, if your change the factor from an interger to a floating point like so.
select (cast ('1' as smallmoney) * 1000000000.0);
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply