Converting varchar to decimal with truncate

Question

I have a column called latitude which is currently varchar(20)

I want to convert it to DECIMAL(9,6)

However the data stored inside the column is greater than 6 decimal points i.e. 48.123456891123456

the table in question has over 50 billion rows and is a 24/7 database with no downtime and uses partitioning by month (SQL server 2017 enterprise)

How would I achieve the conversion as its too big for 6 decimal points. I was thinking of creating a copy of the column and renaming when converted, however i'm not sure how i would achieve the truncate to 6 decimal points

Paul White · Accepted Answer · 2023-04-13T07:22:57.753

If you don't mind rounding, you can do what you want in one step:

ALTER TABLE dbo.Stringy 
    ALTER COLUMN latitude decimal (9, 6) 
    WITH (ONLINE = ON); -- SQL Server 2016 onward

This can be an online operation in SQL Server 2016 or later.

If you need to truncate (not round) things are more tricky. You would need to perform a calculation like:

SELECT 
    CONVERT
    (
        decimal(9,6), 
        ROUND
        (
            CONVERT
            (
                decimal(10, 7), 
                S.latitude
            ), 
            6, -- decimal places
            1  -- truncate, don't round
        )
    )
FROM dbo.Stringy AS S;

If there might be some non-numeric values in there, use TRY_CONVERT instead and accept nulls in the new column, or provide a default value with ISNULL.

If the objective is to save space, you'll need to rebuild the table or run DBCC CLEANTABLE (perhaps in small batches until complete) to achieve that. The dropped variable-length string column will continue to take up space until then.

Converting varchar to decimal with truncate

1 Answers1