Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Scaled-down SQL Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 22, 2011 1:37 AM
 SSC Rookie Group: General Forum Members Last Login: Wednesday, April 09, 2014 4:48 AM Points: 47, Visits: 55
 My answer is like this first part which is i 've answered:Declare @value1 decimal(20,10),@value2 decimal(20,3) SET @value1 = 1234567890.123456789SET @value2 = 0.1SELECT @value1 * @value2Second part is screened answer:DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(30,13) SET @value1 = 1234567890.123456789SET @value2 = 0.1SELECT @value1 * @value2Ans:The screened answer is u 've declared value2 decimal(30,13)... why u need like that?my answer is Declare @value1 decimal(20,10),@value2 decimal(20,3)...... this is enough in SQL server 2005
Post #1081770
 Posted Tuesday, March 22, 2011 3:11 AM
 Hall of Fame Group: General Forum Members Last Login: Thursday, April 03, 2014 10:34 AM Points: 3,352, Visits: 1,478
 pksutha (3/22/2011)My answer is like this first part which is i 've answered:Declare @value1 decimal(20,10),@value2 decimal(20,3) SET @value1 = 1234567890.123456789SET @value2 = 0.1SELECT @value1 * @value2Second part is screened answer:DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(30,13) SET @value1 = 1234567890.123456789SET @value2 = 0.1SELECT @value1 * @value2Ans:The screened answer is u 've declared value2 decimal(30,13)... why u need like that?my answer is Declare @value1 decimal(20,10),@value2 decimal(20,3)...... this is enough in SQL server 2005That's kind of the "moral" of the question. Never use a "bigger" decimal/numeric than you actually need.
Post #1081817
 Posted Tuesday, March 22, 2011 4:04 AM
 Ten Centuries Group: General Forum Members Last Login: Tuesday, July 09, 2013 11:12 PM Points: 1,263, Visits: 1,081
 Duncan Pryde (3/21/2011)michael.kaufmann (3/21/2011)tilew-948340 (3/20/2011)[...][...]Excellent explanation. Couldn't have put it better myself.Thank you very much for your kind words of appreciation, Duncan.Thanks again for your question and great explanation.Regards,Michael
Post #1081847
 Posted Tuesday, March 22, 2011 4:25 AM
 SSCommitted Group: General Forum Members Last Login: Today @ 4:35 AM Points: 1,656, Visits: 5,999
 Duncan Pryde (3/22/2011)That's kind of the "moral" of the question. Never use a "bigger" decimal/numeric than you actually need.The moral I've drawn is that maybe Floats aren't as bad as we thought...
Post #1081861
 Posted Tuesday, March 22, 2011 6:30 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, February 21, 2013 11:34 AM Points: 134, Visits: 97
 Declare @value1 numeric(38,10)Declare @value2 numeric(1,1)SET @value1 = 1234567890.123456789SET @value2 = 0.1SELECT @value1 SELECT @value2SELECT @value1 * @value2= "123456789.012345679"
Post #1081932
 Posted Tuesday, March 22, 2011 6:56 AM
 Hall of Fame Group: General Forum Members Last Login: Thursday, April 03, 2014 10:34 AM Points: 3,352, Visits: 1,478
 rlswisher (3/22/2011)Declare @value1 numeric(38,10)Declare @value2 numeric(1,1)SET @value1 = 1234567890.123456789SET @value2 = 0.1SELECT @value1 SELECT @value2SELECT @value1 * @value2= "123456789.012345679"As expected.Result precision is 38+1+1 = 40, scale is 10+1 = 11. Max allowed precision is 38, so precision and scale are reduced by 2, giving a final result precision and scale of 38,9 - which is why the result is rounded as you can see.
Post #1081947
 Posted Tuesday, March 22, 2011 7:51 AM
 Hall of Fame Group: General Forum Members Last Login: Thursday, April 03, 2014 10:34 AM Points: 3,352, Visits: 1,478
 Toreador (3/22/2011)Duncan Pryde (3/22/2011)That's kind of the "moral" of the question. Never use a "bigger" decimal/numeric than you actually need.The moral I've drawn is that maybe Floats aren't as bad as we thought...Until you want to do something like this
Post #1081991
 Posted Tuesday, March 22, 2011 8:05 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, February 13, 2014 7:49 PM Points: 157, Visits: 280
 Excellent question and nice explanation..Learned something new that Precision and Scale varies for the resulting value based on (+, -, / , *, [UNION | EXCEPT | INTERSECT] , % ) .Thanks for posting this...
Post #1082000
 Posted Tuesday, March 22, 2011 8:25 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 7:28 PM Points: 20,453, Visits: 14,063
 great qeustion Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL ServerSQL RNNRPosting Performance Based Questions - Gail ShawPosting Data Etiquette - Jeff ModenHidden RBAR - Jeff ModenVLFs and the Tran Log - Kimberly Tripp
Post #1082013
 Posted Tuesday, March 22, 2011 8:38 AM
 Ten Centuries Group: General Forum Members Last Login: Thursday, January 31, 2013 8:01 AM Points: 1,232, Visits: 1,046
 Great question. You would not believe how long and how often programers get this wrong. There is even a list that is maintained of ROM chips that do weight conversions incorrectly becuase Dec(6,2) is used instead of Dec(13,5)I even had to "show the math" on this exact thing a year ago when I had to explain why the weight conversion code used to change Pound to Kilos and vice versa was wrong in every appliation where I work. Not understanding this math is why so many ships have a problem balancing thier loads.Sometimes the cargo is weighed in pounds and the balast program uses Kilos.Then someone uses a cheap hand calculator to convert the 100,000 tons in pounds to Kilos and the weghts off by at least 1,000 tons.
Post #1082028

 Permissions