Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

Scaled-down SQL Expand / Collapse
Author
Message
Posted Tuesday, March 22, 2011 1:37 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:47 AM
Points: 47, Visits: 56
My answer is like this
first part which is i 've answered:

Declare @value1 decimal(20,10),@value2 decimal(20,3)

SET @value1 = 1234567890.123456789
SET @value2 = 0.1

SELECT @value1 * @value2

Second part is screened answer:

DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(30,13)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1

SELECT @value1 * @value2


Ans:
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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:55 AM
Points: 3,352, Visits: 1,482
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.123456789
SET @value2 = 0.1

SELECT @value1 * @value2

Second part is screened answer:

DECLARE @value1 DECIMAL(20,10), @value2 DECIMAL(30,13)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1

SELECT @value1 * @value2


Ans:
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


That'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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 9, 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:20 AM
Points: 1,740, Visits: 6,366
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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.123456789
SET @value2 = 0.1
SELECT @value1
SELECT @value2

SELECT @value1 * @value2
= "123456789.012345679"
Post #1081932
Posted Tuesday, March 22, 2011 6:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:55 AM
Points: 3,352, Visits: 1,482
rlswisher (3/22/2011)
Declare @value1 numeric(38,10)
Declare @value2 numeric(1,1)
SET @value1 = 1234567890.123456789
SET @value2 = 0.1
SELECT @value1
SELECT @value2

SELECT @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

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 2:55 AM
Points: 3,352, Visits: 1,482
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 1:05 PM
Points: 157, Visits: 292
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

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 21,744, Visits: 15,435
great qeustion



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1082013
Posted Tuesday, March 22, 2011 8:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse