Changing column from Real to Decimal(18,5) while preserving existing data

Question

Changing column from Real to Decimal(18,5) while preserving existing data

Paul Kraemer 276

Hi,

I am using SQL Server 2012. I have table that has a column named 'Quantity' with Data Type 'Real' and Size = 4. I've been running into some issues related to the imprecise nature of this data type when I try to store large numbers with multiple digits after the decimal place.

With this in mind (unless anyone here advises otherwise), I'd like to change the Data Type from Real to Decimal(18,5). I'd like to do this in a way that will not risk altering any data that I already have stored in this Column.

If anyone can suggest the proper way for me to go about making this change, I would greatly appreciate it.

Thanks in advance,
Paul

Anonymous

2023-04-03T08:26:05.4+00:00

Hi @Paul Kraemer

As Erland says, the data will be altered. And this conflicts with your requirements. Maybe you need to think carefully.
Anonymous

2023-04-14T08:46:26.55+00:00

Hi @Paul Kraemer We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Accepted answer

0 additional answers

Your answer

Anonymous

2023-04-03T08:26:05.4+00:00

Hi @Paul Kraemer

As Erland says, the data will be altered. And this conflicts with your requirements. Maybe you need to think carefully.
Anonymous

2023-04-14T08:46:26.55+00:00

Hi @Paul Kraemer We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

The data will be altered. There is no way around it. The set of values that fit into real is quite disjunct from the set of values that fit into decimal(18,5). Values in the real data type are exact values in the base of 2, values in decimal are exact in the base of 10.

Rather the question is how mutilated will they be? And will it matter in practice? The range for real is from 1E24 to 1E-24, which is a lot wider than decimal(18,5). But I will have to assume that have some idea of the actual range of the values. I would to really expect a column called Quantity to have values like 1E24. Or 1E-24.

To get an idea of the range you can use this query:

SELECT MAX(abs(Quantity)), MIN(abs(Quantity)) FROM tbl

If you have values that are too small to fit into decimal(18,5), you will get zeroes. If they are too big, you will get an overflow error.

All this said, the normal way to do this would be ALTER TABLE ALTER COLUMN. One could think of creating a new table and copy over data, and in this process have special logic to handle difficult cases. But that would be a lot work, and you would have to have quite specific requirements for it to be worth it.

As food for thought, here is a test script, and you can see that some values changes a little bit.

CREATE TABLE #realtable(a real NOT NULL)
INSERT #realtable(a)
    VALUES(1234.234), (0.12345), (7.56E2), (6.722E-10)
SELECT a FROM #realtable
ALTER TABLE #realtable ALTER COLUMN a decimal(18, 5)
SELECT a FROM #realtable
go
DROP TABLE #realtable

Paul Kraemer 276 Reputation points

2023-04-03T21:08:11.94+00:00

Hi Erland,

Thank you for your detailed response.

The maximum value currently stored in this field is 3,000,000. The minimum value is 0. All values are either exactly zero or greater than 1.

It is interesting to me in your test script that a Real value of 1234.234 is changed to 1234.23401 when converted to decimal(18,5).

The specific circumstance that made me consider this change was that a user wanted to enter a value of 196567.644, but no matter what she (or I did), this value was always changed to 196567.641 right after it was entered. (She was actually using an MS Access front end with linked tables, but I tried to enter this value directly in SQL Server Management Studio by right-clicking on the table and choosing 'Edit Top 200 Rows'. Interestingly, rather than trying to type the value in directly, when I tried to set it to 196567.644 using an update query, all I got was 196567.6.

Anyway, all I am hoping to do is choose a data type that will allow entry of values between 0 - 3,000,000 (without worrying about tiny numbers between 0 and 1) and that will reliably allow entry of up to five decimal places. I don't think it will cause any harm if existing values like 1234.234 are changed to 1234.23401 as in your test script, but ideally, I'd rather this convert to exactly 1234.23400.

This brings the following question to mind. Would you think that instead of converting from Real to Decimal(18,5), converting to Float might more accurately preserve my existing data?

Thanks again for all your help.

Best regards,
Paul
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-03T21:27:37.8033333+00:00
This brings the following question to mind. Would you think that instead of converting from Real to Decimal(18,5), converting to Float might more accurately preserve my existing data?

I think a more interesting question is which data type does best represent your actual data? Float and real are mainly intended for scientific data which is inherently accurate. That is, you have a measurement that something weigh 12.345 kg, but any scientist knows that this means that the weight is in the range 12.3445 to 12.3455 kg. But if these values represents something else, users may expect them to be exact. That is, they expect 12.345 to mean 12.345 and nothing else. This is even more pronounced if you do arithmetic operations. For instance, what to you expect from

DECLARE @f1 float = 767882.12345 SELECT round(@f1 /2, 5)

and what do you get? The one problem with decimal is that you need to know the full range of the values. For instance, I worked with a database for many years where we stored money as float. The chief reason for this is that this data model predated the introduction of the decimal data type in SQL Server. And setting the upper range is not trivial, not the least if you need to handle amounts in foreign currencies. But working with float requires that you understand the repercussions of using an approximate data type. And they are the same as with real, but they just strike less often. So unless you have a good mathematical understanding of approximate data types, I strongly recommend that you stick to decimal. Your current values will be altered, but I suspect that they are already mutilated from what the user originally intended. Finally, I converted your Answer to a Comment. Answers are for answers to the original question. As the original poster, you would only post an Answer, if you find the solution yourself.
Paul Kraemer 276 Reputation points

2023-04-04T20:55:29.24+00:00

Thank you Erland, It sounds like decimal will work the best for me. The data we are storing is the Quantity of an Item Ordered on a Purchase Order. We order large quantities of materials for which the way these materials are measured, the Quantity ordered does require several decimal places. Similarly, the prices have multiple decimal places as well.
Our suppliers use all these decimal places when they calculate the amount to invoice us, which is rounded to the nearest cent after the amount is calculated. When we create purchase orders, if we are not able to enter the same quantity and price as that used by our supplier, we are not able to get our calculated purchase order amounts (quantity x price) to match the amount we are invoiced. This has gone unnoticed for quite a few years, but it has caught the attention of our accounting department. After our discussion, I feel pretty safe converting to Decimal(18,5). In actuality, I don't think we ever require more than four decimal places, but I figured I'd allow 5 to be safe. If this changes some of our stored values slightly, you are probably correct that these values (currently stored as Real) are not exactly what the user entered originally anyway. I think I will wait until the weekend (when no one is using the system) to make this change. As our users use an Access front-end with linked tables to SQL server, I believe that after making the change in SQL Server, I will have to re-link the corresponding table in the front-end so that Access "recognizes" that the field is now a different datatype. I'll let you know how this works out and accept the most appropriate one of your posts as the answer. I didn't mean to mark any previous post as an answer, but I suppose I must have inadvertently clicked somewhere I didn't mean to. I appreciate your help - thanks again! Paul
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-04T20:56:50.9366667+00:00

Yes, with your description of the these values are, decimal is definitely a lot better choice. As for the Access part of it, I have nothing to add there, since I've never worked with Access.
Paul Kraemer 276 Reputation points

2023-04-14T16:03:11+00:00

Hi Erland, Over this past weekend, I used the ALTER TABLE ALTER COLUMN instruction you suggested (with the warning that my data would change). The data did change as you warned, but not enough to cause any issues (so far) for the people who use the system and look at reports. For calculated totals on Purchase Orders, they previously were never able to get the result the wanted when the quantity was a fairly large number with multiple decimal places anyway, so its likely that small changes to past purchase order quantities that resulted from my conversion will go unnoticed (I hope). At least now moving forward, they can enter the exact values they want and the calculations are predictable. I really appreciate your help! Best regards,
Paul
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-14T20:51:32.65+00:00

Great to hear that things worked out!

Share via

Changing column from Real to Decimal(18,5) while preserving existing data

0 additional answers

Your answer