January 4, 2019 at 6:47 am
We have a table containing INT values that are entered by the user. There are many millions of rows. The data represents a whole percentage (e.g. 20 = 20%). This data is calculated against a float value in another table representing a cost. The calculation typically takes the form of: (floatCost * intPercentage) / 100. This data has been in this format since the inception of the application.
The customer now wants to allow users to enter up to a two digit decimal in the percentage table. I'm trying to determine the impacts to historic data. To do that I created an int table containing values 1-100. I then ran the following query to try to identify whether or not the data type will affect the calculation. This simulates every currently possible percentage against every known cost value.
SELECT * FROM
(
SELECT
(CONVERT(INT, PCT.val) * PROD.cost) / 100 as intCalc,
(CONVERT(float(7) , PCT.val) * PROD.cost) / 100 as floatCalc,
(CONVERT(decimal(6,2), PCT.val) * PROD.cost) / 100 as decimalCalc
FROM #PCT PCT, productionCosts PROD
) D WHERE
intCalc <> floatCalc OR intCalc <> decimalCalc
As I expected I saw no differences in the resulting calculation. This leads me to believe that at least these historic calculations are safe.
Questions for the experts:
1) Is this a valid test or am I just making myself feel better?
2) Does anyone have experience with this type of conversion and can offer advice and pitfalls?
3) Given that the percentage table will not hold more than 2 decimals is there an advantage of using a decimal data type (5 bytes) over float (4 bytes., same as current INT storage) considering that this data is always calculated against a float value representing a cost?
Any other thoughts would be greatly appreciated.
ST
January 4, 2019 at 7:55 am
There are a lot of articles on why float is not an accurate datatype to be using even on this site as well as Books Online. With that said, how about using vardecimal? What about all the calculations that are expecting to have to divide by 100? I would also be concerned about your Cartesian join.
From Books online (and Gail Shaw here (https://www.sqlservercentral.com/Forums/434143/Diff-between-Numeric-Float))
The float and real data types are known as approximate data types. The behavior of float and real follows the IEEE 754 specification on approximate numeric data types.
Approximate numeric data types do not store the exact values specified for many numbers; they store an extremely close approximation of the value. For many applications, the tiny difference between the specified value and the stored approximation is not noticeable. At times, though, the difference becomes noticeable. Because of the approximate nature of the float and real data types, do not use these data types when exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks. Instead, use the integer, decimal, money, or smallmoney data types.
The numeric data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The numeric data type stores an exact representation of the number; there is no approximation of the stored value.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 4, 2019 at 8:16 am
Thanks for the reply. What are your concerns with the cartesian join?
January 4, 2019 at 9:32 am
I don't know your data, but you are taking every record in the PCT table and applying it to every record in the ProductionCosts tables. Does the PCT table represent ExchangeRates? Discounts? Markups?
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
January 4, 2019 at 9:46 am
Yes, The reason for the cartesian join is to simulate every possible calculation in the current data set to see of the result is the same when handled as an INT vs FLOAT vs Decimal. This is a test only. Not something I'm doing to affect the real data.
January 4, 2019 at 9:55 am
souLTower - Friday, January 4, 2019 9:46 AMYes, The reason for the cartesian join is to simulate every possible calculation in the current data set to see of the result is the same when handled as an INT vs FLOAT vs Decimal. This is a test only. Not something I'm doing to affect the real data.
If you check the execution plan when using INT you will most likely see that the INT values are implicitly converted to float values prior to the computation.
January 4, 2019 at 10:04 am
Great idea, I'll look at the execution plan. Thanks for the tip.
January 4, 2019 at 10:04 am
As PROD.cost is a float, I think all values in the expression will be converted to float to do the calculation.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply