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