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?

Answer 1

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'

Answer 2

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