January 23, 2012 at 9:56 am
garystephen (1/23/2012)
1. Why did T-SQL correctly infer that I wanted a decimal value when I did (INTEGER*.02) but not when I did (INTEGER/50)? The statements are mathematically equivalent, and it can't be an order of operation issue because multiply and divide are at the same level (and I used parens to dictate the order of operation anyway).
Because 0.02 is a decimal (or float) value, 50 however is an integer. The first is Integer*decimal and the type precedence makes the result a decimal. The second is Integer/Integer and that give back an Integer result, there's no other types involved, so no conversions.
2. From a performance standpoint, what's the best approach to a situation like this? Add the extra CAST statement when needed, make an integer column decimal if I might need a function that converts it to a decimal, or fudge the formula so the implicit conversion works?
If you like surprises, reply on implicit conversion. It's not a performance question, it's a question of correctness of data and explicitness of code. Incorrect results is not useful.
p.s.
UPDATE TABLE SET COLUMN_A=COLUMN_A+((CAST COLUMN_B AS DECIMAL)/50)
Do you know offhand what the default precision and scale is for decimal? I certainly don't.
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
January 23, 2012 at 10:53 am
Thanks for your reply. That helps a lot.
Do you know offhand what the default precision and scale is for decimal? I certainly don't.
So I should do (CAST COLUMN_B AS DECIMAL(3,2)) instead. Gotcha.
January 23, 2012 at 11:09 am
garystephen (1/23/2012)
So I should do (CAST COLUMN_B AS DECIMAL(3,2)) instead. Gotcha.
Well, (3,2) or whatever precision and scale are appropriate for your particular case.
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
January 23, 2012 at 11:32 am
As long as one of the values is a decimal, the "/" will return a decimal value. So this will also work:
UPDATE TABLE SET COLUMN_A=COLUMN_A+(COLUMN_B/50.0)
However, Gail's comments about lack of precision still applies.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
January 23, 2012 at 11:37 am
WayneS (1/23/2012)
As long as one of the values is a decimal, the "/" will return a decimal value. So this will also work:
UPDATE TABLE SET COLUMN_A=COLUMN_A+(COLUMN_B/50.0)However, Gail's comments about lack of precision still applies.
And with that there's always the chance that some future developer's not going to understand what's happening and takes out the .0 because it's unnecessary.
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
January 23, 2012 at 12:55 pm
GilaMonster (1/23/2012)
WayneS (1/23/2012)
As long as one of the values is a decimal, the "/" will return a decimal value. So this will also work:
UPDATE TABLE SET COLUMN_A=COLUMN_A+(COLUMN_B/50.0)However, Gail's comments about lack of precision still applies.
And with that there's always the chance that some future developer's not going to understand what's happening and takes out the .0 because it's unnecessary.
Ahh yes, there is always that. So some comments in your code about it's use would be appropriate, but still not foolproof.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply