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: Friday, May 15, 2015 1:33 AM Points: 47, Visits: 58
 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, October 13, 2016 4:04 AM Points: 3,358, Visits: 1,552
 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: Monday, March 16, 2015 1:09 AM Points: 1,263, Visits: 1,082
 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
 SSCrazy Group: General Forum Members Last Login: Wednesday, November 30, 2016 2:06 AM Points: 2,251, Visits: 8,029
 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: Monday, November 2, 2015 2:17 PM Points: 134, Visits: 104
 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, October 13, 2016 4:04 AM Points: 3,358, Visits: 1,552
 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, October 13, 2016 4:04 AM Points: 3,358, Visits: 1,552
 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: Wednesday, August 26, 2015 3:27 PM Points: 169, Visits: 325
 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: Today @ 2:52 PM Points: 20,083, Visits: 18,257
 great qeustion Jason AKA CirqueDeSQLeilI have given a name to my pain...MCM SQL Server, MVPSQL RNNRPosting Performance Based Questions - Gail Shaw
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,228, 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