change SQL column from Float to Decimal Type

Question

CREATE TABLE [dbo].[TES_Tracks](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Rate] [float] NULL,
[TOL] [datetime] NOT NULL
)

I want to change rate column to decimal(28,6). I already have lot of data in this table in float format. i'm scared of any data loss. how should i go at this?

Solution 1

You can simply update the Rate data and then change the column data type.

First, you can verify the CAST by using the following query (for only rows that have the decimal part < 0.000001)

SELECT 
  [Rate],
  CAST([Rate] as decimal(28, 6)) Rate_decimal
FROM [dbo].[TES_Tracks]
WHERE [Rate] - FLOOR([Rate]) < 0.000001;

Once you have verified that the CAST expression is correct, then you can apply it using an UPDATE statement. Again, you can update only those rows which have [Rate] - FLOOR([Rate]), thus getting good performance.

UPDATE [dbo].[TES_Tracks]
SET [Rate] = CAST([Rate] as decimal(28, 6))
WHERE [Rate] - FLOOR([Rate]) < 0.000001;

ALTER TABLE [dbo].[TES_Tracks] ALTER COLUMN [Rate] DECIMAL(28,6);

This way, you would not need to drop the Rate column.

SQL Fiddle demo

Solution 2

Untested but his may be worth a try...

ALTER TABLE [dbo].[TES_Tracks] ADD [RateNew] DECIMAL(28,6);

UPDATE [dbo].[TES_Tracks] set RateNew = Cast(Rate as Decimal(28,6));

since sql server handles decimal vs float implicitly, this should get you rows if you have data loss.

Select * From [dbo].[TES_Tracks] where Rate <> RateNew;

Then if you are satisfied...

ALTER TABLE [dbo].[TES_Tracks] DROP COLUMN [Rate];

Then rename the RateNew column to Rate

EXEC sp_RENAME 'TES_Tracks.RateNew' , 'Rate', 'COLUMN'

Comments

[CoderKK]

Thanks for the answer.but droping the Column Rate is not an option since it's been used in tons of other places. therefore, your method is bit error prone in my senario.

[FumblesWithCode]

In that case why not use the new (temporary) column to repopulate Rate after you alter it to decimal. In other words, add RateNew and UPDATE as bsivel suggested. Then ALTER table to change the datatype as Lamak suggested. Then you can compare Rate to RateNew and update from RateNew if the column change caused any rounding issues that you did not like. Finally, drop RateNew.

[CoderKK]

Thanks! that will help

[CoderKK]

@JoesephB may be a dumb question but i don't understand why you used [Rate] - FLOOR([Rate]) in the where clause.

[Joseph B]

@CoderKK No worries. Using [Rate] - FLOOR([Rate]) to get the decimal part of Rate (for e.g. if Rate=15.000001, then [Rate] - FLOOR([Rate]) = 0.000001.

[Gary Bao 鲍昱彤]

I simply tried ALTER TABLE [dbo].[TES_Tracks] ALTER COLUMN [Rate] DECIMAL(28,6) and it worked, so my question is what's the difference between this simple method and the more lengthy one here?

[youcantryreachingme]

I believe this logic is incorrect - the cast statement in your update statement will round your floats so the very small fractional parts are truncated, but when the system attempts to persist this truncated decimal value back into your column, it will implicitly convert back to float again - with no guarantee that the decimal you generated can be mapped into a float without introducing another small fractional part. In other words, the update step is not only useless, but may actually update the data values to something new.

[youcantryreachingme]

Additionally, your where clause should look at the absolute value of the difference. It is conceivable the small fractional part of your float is just below a precise decimal value. The concept behind your select is sound - but I doubt you would actually see any difference in values when viewing just 6 decimal places. Rather, it might pay to look at the difference between your new decimal value and the original float. Ultimately the alter statement is the approach to take but the instructions here don't really address how to be confident about what data gets lost.