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

Paul Kraemer 276 Reputation points
2023-04-02T17:19:39.7+00:00

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

SQL Server | Other
{count} votes

Accepted answer
  1. Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
    2023-04-02T18:09:30.0366667+00:00

    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
    

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.